Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: 12M Rolling Chart

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

18 Replies
roharoha
Valued Contributor III

Re: 12M Rolling Chart

could you post a sample please.

JustinDallas
Valued Contributor II

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

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

Not applicable

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

Which field are you making selection in?

Not applicable

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

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

Community Browser