Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think that should work?!?
--> or is there again a problem with derived fields?
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
What is the script used to create Report Date? Can you share a sample?
Text boxes are similar to KPI Objects in Qlik Sense
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])
;
Try this change
LOAD Date(Floor(MonthEnd("Transaction Date"))) as "Report Date"
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
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
Cool.
will keep this in mind.
thanks again.