Search code examples
dashboardqlikviewqliksenseqlik-expression

How to limit data on line chart to latest month on QlikSense?

I'm trying to create a line chart with the data for only the current month. My date field is called PurchaseDate. I use this also for the 'Date Range Picker' widget in my dashboard. My dimension is PurchaseDate and my measure is PurchaseVolume. My data looks like this:

enter image description here

How can I set a condition to use only the current/latest month for my line chart?

I tried using a variable like this:
CurrentMonth = Max(PurchaseDate)

And I set the expression in my line chart to:
Dimension: if(CurrentMonth, PurchaseDate)
Measure: if(CurrentMonth, PurchaseVolume)

But here, my values keep changing according to the selected date on the Date Ranger Picker. How can I set the line chart for only the latest month (so the dimension will have each day of the latest month, which in this case will be Jan 2022) independent of the Date Range Picker? Is there a way I can set the calculation condition?

Solution

  • You can use set analysis in your expression to filter PurchaseDate(s) which are between the MonthStart and MonthEnd of the max possible month:

    Sum({<PurchaseDate={">=$(=Monthstart(max({1}PurchaseDate)))<=$(=monthend(max({1}PurchaseDate)))"} >} PurchaseVolume)

    The interesting path is the set analysis:

    PurchaseDate={">=$(=MonthStart(max({1}PurchaseDate)))<=$(=MonthEnd(max({1}PurchaseDate)))"}

    • Monthstart(max({1}PurchaseDate) will return the start date of the max possible date
    • MonthEnd(max({1}PurchaseDate) will return the end date of the max possible date
    • the {1} before MonthStart and MonthEnd functions is to ignore all possible selections when calculating

    The result table is:

    Between dates

    Having month end field

    Alternative of between two dates is to have another field in the table (or in the calendar table, if exists). This field will be associated with each date and will represent the month of the date. For example:

    table view

    In this case the expression will be:

    Sum({<PurchaseDate=, PurchaseMonth={"$(=max({1}PurchaseMonth))"}>} PurchaseVolume)

    (PurchaseDate= is to initially to ignore all selections in PurchaseDate field)

    And the result of both tables will be the same:

    all tables

    For the record, my script is:

    RawData:
Load
  *,
  MonthEnd(PurchaseDate) as PurchaseMonth
;
Load * Inline [
PurchaseDate, PurchaseVolume
11/01/2022  , 100
12/01/2022  , 101
13/01/2022  , 102
14/01/2022  , 103
01/02/2022  , 104
02/02/2022  , 105
03/02/2022  , 106
04/02/2022  , 107
01/05/2022  , 108
02/05/2022  , 109
03/05/2022  , 110
04/05/2022  , 111
];