How to create a LYTD time bucket

[Michael_Chappell]

Good Afternoon all,

We are fairly new to the Qlik Sense scene and although we have made great progress in developing our first App, we are being held back by one particular element: LYTD.

We currently have a working YTD with the expression being loaded directly into the Data Load, as shown below;

IF( DayNumberOfYear([Date]) <= DayNumberOfYear(Today()-1) AND
[Date] <= Today()-1, 'Y', Null() ) AS IsYTD

We are trying to use a similar method to use LYTD so that YTD and LYTD can be shown side-by-side in a table to compare each but can't get it to work. Any help would be greatly appreciated! 

[Kushal_Chawda · Accepted solution]

try this

if(inyeartodate(Date,today(),0) ,'Y' )as YTDFlag,
if(inyeartodate(Date,today(),-1) ,'Y') as LYTDFlag,

[Michael_Chappell]

Hi Kush,

Thank you for your reply.

The Data Load loads fine with these two lines, however they only work under the current or last year when applied to a Pivot Table. I have attached a screenshot showing this. Is it possible to get these showing side by side? (The expression we use to show the sales value is Sum( {<YTDFlag = {'Y'} NetSalesValue) which I have changed for the purposes of the screenshot)

[Kushal_Chawda]

probably it will be easier if you just have year dimension but if you adding more then it will get complex. When you expand to Month & Date how would you like to see the values?

For eg. If you want to expand month , Jan 2020, then for this you want the value of Jan 2019 in LYTD column?

[Michael_Chappell]

That's correct. This way it will be easier for us to produce comparisons.

[Michael_Chappell]

Ah! We've managed to get your expression working Kush. By taking out InvoiceYear as a Measure the Pivot Table now accurately shows YTD vs LYTD.

Thank you for your help!