Customised Date Range

[anything200]

Hi all,

I want to create a table, which shows number of purchases by product Year to date.

I have 2 dimensions, the 1st is the product the second is the Year to date.

The issue I have is with the Year to date. Essentially this runs along with the Tax Year so 01/04 to 31/03... But I cant get it to work when the report date is January. It just counts Januarys purchases of the current year and not from April last year to now.

This is what I have so far

=if(num(month(vReportDateName)) > num(month([Purchase Date])),

if(num(month([Purchase Date])) >= 4 and num(month(vReportDateName)) >=4 , '01/04/' & year([Purchase Date])& ' - ' & date(vReportDateName, 'dd/MM') & '/'& year([Purchase Date]) ,

if(num(month([Purchase Date])) <= 3 and num(month(vReportDateName)) <=3 , '01/04/' & year(addyears([Purchase Date],-1))& ' - ' & date(vReportDateName, 'dd/MM') & '/'& year(addyears([Purchase Date],-1))
)) )

[Arthur_Fong]

Try this for all date related expression:

addmonths([Purchase Date],3,0)     

[anything200]

Hi Arthur_Fong,

Thank you for the reply, but this isn't exactly what I was after. Im not sure where I would put it to make it work.

An example of what I want to see is, if today is the 23rd January 2020, I want to have the total number of sales of each product from 1st April 2019 to the 23rd January 2020.

At the moment it works for all months after April, so if today was the 23rd December it will show me all sales from 1st April to today but not for the first 3 months of next year.

[Brett_Bleess]

Lucy, check the following Design Blog post, it may be of some help to you on your use case:

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

Regards,
Brett