0

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?

user42
  • 871
  • 1
  • 10
  • 28
  • Just to confirm. The chart should always show the last possible month (from `PurchaseDate` field) no matter what is selected (or not) in the `PurshaseDate` field? – Stefan Stoichev Jun 09 '22 at 13:19
  • That's right, I want to have an overview of the latest month that's in the database – user42 Jun 09 '22 at 13:25

1 Answers1

2

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
];
Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51
  • Thank you! What do you mean by using `{1} ignore all possible selections when calculating`? – user42 Jun 13 '22 at 13:15
  • 1
    Qlik allows to "put" your data in different states. The "default" state for the whole app is `$`. All set analysis is defaulting to `$` state as well. `sum( {< ... >} Sales)` and `sum( {$< ... >} Sales)` are equal and will yield the same result. Apart from `$` there is another special state and thats `1`. When using `1` in set analysis then the expression will not be affected by any selections in any state. So if you have `sum( {1< ... >} Sales)` expression then no matter what is selected across the app the expression will show the same result (as if no selections at all are made) – Stefan Stoichev Jun 13 '22 at 14:28