Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

robin_hausdoerfer
Valued Contributor III

Re: 12M Rolling Chart

I think that should work?!?

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

robin_hausdoerfer
Valued Contributor III

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

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

Re: 12M Rolling Chart

Text boxes are similar to KPI Objects in Qlik Sense

Not applicable

Re: 12M Rolling Chart

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])

    ;

Re: 12M Rolling Chart

Try this change

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

Not applicable

Re: 12M Rolling Chart

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

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

Not applicable

Re: 12M Rolling Chart

Cool.

will keep this in mind.

thanks again.