Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fredericlehner
New Contributor II

YTD ane previous month based on selected month

Hi,

My script :

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -1)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1), 1, 0) As 'P1M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -13)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13), 1, 0) As 'P1M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -3)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1), 1, 0) As 'P3M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -15)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13), 1, 0) As 'P3M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -12)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1),1, 0) As 'P12M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -24)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13),1, 0) As 'P12M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= YearStart(Today(), 0)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), 0), 1, 0) As 'YTD',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= YearStart(Today(), -1)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= AddYears(Today(), -1), 1, 0) As 'YTD-1',

---------------------------------------------------------------------------------------------------------------------------

I would like the user to be able to choose the end date (reference date).

Example: choose 13 march 2019 -> P1M = February 2019 and P1M-1 = February 2018. etc. for PM3, P3M-1, ..., YTD, YTD-1.

What to change in the script?

Thank very much for your help.

Fred

Labels (4)
2 Replies
Ezir
Contributor II

Re: YTD ane previous month based on selected month

Hi @fredericlehner ,

As the user can choose the reference date, it is more appropriate to work with variable in the dashboard.

In the script you would just load [Completed Date] and in the dashboard create variables such as "P0", "P1M", "P1M-1" etc ...

In your panel, I suggest the extension Date picker or Variable input

 

I hope helps.

fredericlehner
New Contributor II

Re: YTD ane previous month based on selected month

Thanks Ezir !

In my next version, I will implement your proposal.

But, any solution exist with my situation ?

Thank you for your help !

Fred