Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Ashwinyp
Contributor III
Contributor III

Cumulative values for rolling 6 months based on date selected

I am trying to create a chart that gives me cumulative value for rolling 6 months based on the date selected.

My data has 4 types of status dates at Application level (There are also many more columns in the dataset). For example, an application can move from Status 1 to Status 2 to Status 3 to Status 4 on different dates. I want to add a common running date filter based on which the number of applications should be filtered (because neither of the 4 columns have exhaustive list of dates). For this, I imported another data file containing running date for a period of about 3 years and used it as filter (full_date).

I have also extracted the day out of each date as day_s1date, day_s2date, etc.

Table1:

ApplicationIDS1_dateS2_dateS3_dateS4_date
107/09/201810/09/201811/09/201817/09/2018
206/11/201808/11/2018  
310/11/2018   
420/06/2018   

 

Table2:

Full_date
01/01/2017
02/01/2017
03/01/2017
...
...
...
31/12/2019

 Based on the date selected, the output should show cumulative count of applications from month start date till that date for rolling 6 months. If I select 15/11/2018 from the filter, I need count from 1 to 15 for June'18 to Nov'18.

Cumulative monthS1 countS2 countS3 countS4 count
Jun'18    
Jul'18    
Aug'18    
Sep'18111 
Oct'18    
Nov'1821  

 

I have used this for Dimension =(Date(monthstart(full_date), 'MMM-YY'))

I have used this for Measure

S4 count:

count({<
S4_date={">=$(=date(Max(full_date)-180,'DD/MM/YYYY'))<=$(=max(full_date))"},
Day_s4date={"<=$(=Day(Max(full_date)))"}
>}DISTINCT (APPLICATIONID))

S3 count

count({<
PRODUCT_LEAP={'HE'},
S3_date={">=$(=date(Max(full_date)-180,'DD/MM/YYYY'))<=$(=max(full_date))"},
Day_s3date={"<=$(=Day(Max(full_date)))"}
>}DISTINCT (APPLICATIONID))

But this is not giving me the split at month level. I am getting only the total value for the 6 months like:

Cumulative monthS1 countS2 countS3 countS4 count
Nov'18321 

 

Can someone help me how to get the month-wise count?

3 Replies
sunny_talwar

I am not entirely sure, but I think you might need to use an if statement instead of using set analysis to get what you are looking to get here

Ashwinyp
Contributor III
Contributor III
Author

Can you please suggest where the "IF" statement has to go in the code?

sunny_talwar

It would be within the count function, but I don't think I can give you an exact expression without looking at what you have. Would you be able to share a sample to check this out?