Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
debimorr
Contributor
Contributor

Year Ago Sales

I know that this has been mentioned before, but I am need of assistance with year ago sales. 

My data table has daily sales with sales in one column (called Amount) and Date in another column (Called Date).  I am using an auto calendar in my sheet. 

I can get current sales sales by picking a date in the filter pane for date, but no formula I have tried using can give me the previous year's number (for the same day last year).  My Total Sales formula is a little complicated, so I created a Master Item for it called Sales.

This has to be so simple.  Can anyone tell me what am I missing?  

I can not use a script for this. 🙂

Thank you

 

1 Reply
Ezir
Creator II
Creator II

Hi @debimorr ,

One suggestion is...

1) Add a field MONTH+DAY (in script)

 

num(month(floor(DAT_SALE)),'00')&num(day(DAT_SALE),'00') as MONTH_DAY,
num(year(DAT_SALE)) as YEAR

 

2) Expression to current year

 

SUM({<YEAR={"$(=num(year(now())))"}, MONTH_DAY = {"<=$(=num(month(floor(now())),'00')&num(day(now()),'00'))"}>} VAL)

 

3) Expression to previous year

 

SUM({<YEAR={"$(=num(year(now()))-1)"}, MONTH_DAY = {"<=$(=num(month(floor(now())),'00')&num(day(now()),'00'))"}>} VAL)

 

 

Best regards

 

Ezir