Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Austin
New Contributor

Dynamic Date Ranges

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')

 

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Dynamic Date Ranges

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!

3 Replies
Frank_Hartmann
Honored Contributor II

Re: Dynamic Date Ranges

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
New Contributor

Re: Dynamic Date Ranges

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
Honored Contributor II

Re: Dynamic Date Ranges

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!