Dynamic Date Ranges

[Austin]

Context

I'm developing an app which generates graphs from data in an excel document each month. Instead of having the end-user change the date format in the source document each month, I created a field in the app which interprets and converts the date format into a date field (See below code).

Issue

One of the graphs displays the amount of charges (Measure) each month (Dimension). However, the graph shows 8 months and the user has requested the graph to show the last 6 months. How do I make the graph (1) display the last 6 months and (2) make the expression dynamic so it automatically displays the last 6 months when I upload new data each month?

Date(Date#(Date, 'YYYY/MMM'),'MM/YYYY')

[Frank_Hartmann]

Something like this should work:

Script:

FactTable:
LOAD Date(Date#(Date, 'YYYY/MMM'),'MM/YYYY') as Date, 
     Charges
FROM
[C:\Users\admin\Desktop\Example.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join

tmp1:
Load max(Date) as max Resident FactTable;

Chartdimension:

=If(Date > Date(MonthStart(Date(max,'YYYY/MM'), -6),'YYYY/MM') and Date <= Date(max,'YYYY/MM'), Date)

Expression:

sum(Charges)

[Austin]

1. Do I enter the chart expression under Sorting --> Date --> Sort by expression?

2. I didn't use a script--I created the date field manually. Where is the option to write scripts located?

Excuse the additional question, I'm brand new to Qlik Sense and have been unable to locate any lists of functions or even good training materials.

[Frank_Hartmann · Accepted solution]

Sorry didnt realized that you are working on QlikSense.

Without changing the script (which you can call by clicking on the upperleft navigation button and select dataeditor)

you can use this expression for the dimension on data tab (not sorting tab!!)

If(Date > MonthStart(Date(max(Total Date),'YYYY/MM'),-6) and Date <= Date(max(Total Date),'YYYY/MM'), Date)

As KPI (on data tab) use this expression:

sum(Charges)

This will show you the last 6 month!

hope this helps!