Sales for the previous year.

[ninikvl]

Hello,

I’m new to Qlik and I need to display sales data for the previous year.
Data for example:

When I select a date range from 01.01.2024 to 03.01.2024 using a Date Picker, I want to generate a table showing the corresponding data for the same date range in the previous year.

I’ve tried writing a formula to calculate the sales from the previous year, but it doesn’t seem to work. Here is the formula I’m using:
=SUM({<date = {'>=$(=Date(AddYears(Min(date),-1))) <=$(=Date(AddYears(Max(date),-1)))'}>} sales)

Could you please help me identify what’s wrong or suggest a solution?

[Vegar]

It looks like you're on a right track, but using single quotes you are doing a string comparison in your modifier, but to do a search on your defined interval you need double quotes. 

Try replacing your ' with " like this:

=SUM({<date = {">=$(=Date(AddYears(Min(date),-1))) <=$(=Date(AddYears(Max(date),-1)))"}>} sales)

See more on modifiers here: Set analysis - Set modifiers 

[ninikvl]

I tried changing the single quotes to double quotes, but unfortunately, it still doesn't work, and the field continues to show 0.

[Kushal_Chawda · Accepted solution]

@ninikvl  Your expression looks fine. Make sure that date is in proper date format. check if date field is right aligned when you take it in filter, if not you need to convert it into proper date format using date#() fucntion then try below. Also, it might be possible that you have other selections in your dashboard for which there is no data for selected date of previous year. Hence, try to change your date selection or other filters to see if it works

=SUM({<date = {">=$(=AddYears(Min(date),-1)))<=$(=AddYears(Max(date),-1))"}>} sales)

[ninikvl]

Thank you for your help! The issue was with the date format in my data, which was YYYY-MM-DD, while the expression inside the < > was formatting it as DD-MM-YYYY. I corrected the format, and now everything works perfectly.