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

18 Replies
Anonymous
Not applicable
Author

I think that should work?!?

--> or is there again a problem with derived fields?

Anonymous
Not applicable
Author

you could try

$(=MonthStart(Max([Report Date]),-11))


and


$(=Date(Max([Report Date])))


in 2 separate  text boxes (don't know how it is called in sense) and post the result

sunny_talwar

What is the script used to create Report Date? Can you share a sample?

sunny_talwar

Text boxes are similar to KPI Objects in Qlik Sense

Not applicable
Author

I am not sure what you meant there.

But below is the code from editor.

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

and then I sum  up my sales as follows inside the fact.

   ,Sum("USD Netflows") as NetFlows

    ,Sum("USD Inflows") as Inflows

    ,Sum("USD Outflows") as Outflows

    Resident Fact

    group by [Report Date], Year([Report Date])

    ;

sunny_talwar

Try this change

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

Not applicable
Author

Indeed!

it worked. thanks much.

can you please explain, why that would work. not to forget that the expression was given by a qlik consultant

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

Not applicable
Author

Cool.

will keep this in mind.

thanks again.