Yet another this period vs last year comparison question

[rasmusnielsen]

Hi all,

Consider this data model (I have attached the Qlik Sense file as well):

date	station	product	volume
2020-04-03	100	1	300
2020-04-02	100	1	200
2020-04-01	100	1	100
2019-04-03	100	1	70
2019-04-02	100	1	80
2019-04-01	100	1	90

I wish to create this straight table:

However,  I cannot seem to figure out how to calculate the Last Year data. I expect the data for 2019 to be shown. So for 2020-04-03 I would have the volume for 2019-04-03 in last year column: 70.

I am going to have the last year value displayed in different charts, where the date dimension is not added, where I expect this task to be easier, but since date is a dimension in this table, I am struggling to find a solution.

I have tried

Sum({1<date={"=$(=AddYears(date, -1))"}>}volume)

 Also fiddle around with TOTAL and p(), but not entirely understanding how to solve this.

Appreciate any pointers 🙂

Thanks

[lorenzoconforti · Accepted solution]

You need to manage it at a data level; see attached

[rasmusnielsen]

Thanks for your reply - works as expected. 🙂

But is this really not possible with set expressions?

[rasmusnielsen]

For others who might be interested, this is the suggested load script:

Data:
Load * Inline [
	date, station, product, volume
    2020-04-01, 100, 1, 100
    2020-04-02, 100, 1, 200
    2020-04-03, 100, 1, 300    
    2020-02-29, 100, 1, 400    
    2019-04-01, 100, 1, 90
    2019-04-02, 100, 1, 80 
	2019-04-03, 100, 1, 70 
    2019-02-28, 100, 1, 10
    2019-03-01, 100, 1, 20
];

left join (Data) load date(AddYears(date,1),'YYYY-MM-DD') as date , volume as volume1Y Resident Data;

[lorenzoconforti]

Not in a table; what you need to consider is that, when in a table, the qlik engine has no visibility of the dimension value. In your case, in the first row (date 2020-03-04) you don't have access to the value of the date (2020-03-04) to be able to subtract 1 year and get the resulting volume from 1 year earlier.  You've tried this expression:

Sum({1<date={"=$(=AddYears(date, -1))"}>}volume)

This would work in a KPI or when working with a single date because the $ expansion is evaluated once (you can see this in the expression editor in the info box at the bottom); so, if you have multiple values for date it won't work

Often the solution used is to use above/below to access records on different rows in the table (i.e. the output table); in your case it wouldn't be recommended as you wouldn't know easily how many rows below the current one is the date you are looking for

[rasmusnielsen]

Makes sense - thanks for the elaboration! 🙂

[killersswang]

How about create an additional field "Month-Day" in Master Calendar, and using this Month-Day as Dimension, create another tow measure to calculate the volume of this and last year by using set analysis function

=sum({$<Year={$(  =year  (max  ( Date) ) )      } >    } Volume)

=sum({$<Year={$(=year(max(Date))-1)  }>     }Volume).

Just suggestions.

Month-Day	This Year 2020	Last Year 2019
04-01	300	70
04-02	200	80
04-03	100	90