Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

12M Rolling Chart

Can you please help with the below expression

I have created a bar chart to show data for 12 Month Rolling Period.

Without any filtering, I see the data rolled for 12 Months correctly.

But once I start, selecting the values in the Filter the Dashboard doesn't filter correctly to show 12month it selects from the month selected and forward. please help.

Max(Report Date) = '7/31/2017'

sum(

{<

[Report Year]=,

[Report Month]=,

[Report Date]={'>=$(=MonthStart(Max([Report Date]),-11))<=$(Date(Max([Report Date])))'}

>}

NetFlows)/1000000

Without Selection:

Rolling_12M_Qlik.png

With selection on May 17

Rolling_12M_Qlik_Error.png

1 Solution

Accepted Solutions
sunny_talwar

This here gives a timestamp like this 08/31/2017 11:59:59 pm. So, although you did use Floor, but MonthEnd changed the  date back to timestamp

MonthEnd(Date(Floor("Transaction Date"))) as "Report Date"

On the other hand, this will give you 08/31/2017 00:00:00 am. So, first you find the Monthend (timestamp) and floored and use the date function for formatting.

Date(Floor(MonthEnd("Transaction Date"))) as "Report Date"

When you used the 1st one in set analysis, there are some rounding issues which might have caused you to not see the max month.... but when you used the new definition, no more rounding problems as we got rid of the time portion of the timestamp

View solution in original post

18 Replies
Anonymous
Not applicable
Author

could you post a sample please.

JustinDallas
Specialist III
Specialist III

Do you want the chart to reflect the user selection, or do you want a Rolling 12 Months that ignores what the user selects?

Not applicable
Author

Yes. I want the chart to reflect the user selection.

Not applicable
Author

I am posting the Sample Data below

 

Report DateNetflows
7/1/201450000
8/1/201420000
9/1/201410000
10/1/201440000
11/1/201470000
12/1/201460000
1/1/201550000
2/1/201550000
3/1/201520000
4/1/201510000
5/1/201520000
6/1/201510000
7/1/201540000
8/1/201570000
9/1/201560000
10/1/201550000
11/1/201550000
12/1/201520000
1/1/201610000
2/1/201640000
3/1/201670000
4/1/201660000
5/1/201650000
6/1/201650000
7/1/201620000
8/1/201610000
9/1/201640000
10/1/201670000
11/1/201660000
12/1/201650000
1/1/201750000
2/1/201720000
3/1/201710000
4/1/201740000
5/1/201770000
6/1/201760000
7/1/201750000
8/1/201750000
sunny_talwar

Which field are you making selection in?

Not applicable
Author

There is a Filter for Report Date on the Page.

user would want to select that, and the Rolling bar Chart should display the data accordingly.

i.e. for May 17 - It should show June 16 - May 17

Not applicable
Author

Report Date is basically has the below data.

 

Report Date
7/1/2014
8/1/2014
9/1/2014
10/1/2014
11/1/2014
12/1/2014
1/1/2015
2/1/2015
3/1/2015
4/1/2015
5/1/2015
6/1/2015
7/1/2015
8/1/2015
9/1/2015
10/1/2015
11/1/2015
12/1/2015
1/1/2016
2/1/2016
3/1/2016
4/1/2016
5/1/2016
6/1/2016
7/1/2016
8/1/2016
9/1/2016
10/1/2016
11/1/2016
12/1/2016
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
6/1/2017
7/1/2017
8/1/2017
sunny_talwar

I think you forgot to add an equal sign... try this

Sum(

{<

[Report Year]=,

[Report Month]=,

[Report Date]={'>=$(=MonthStart(Max([Report Date]),-11))<=$(=Date(Max([Report Date])))'}

>}

NetFlows)/1000000

Not applicable
Author

I tried that too.

But it display's the data only till June'17 in default selection, though the data is available for Max Report Date as July.

And any selection on report date shows for a month less.

user_selection.png