Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## Yet another this period vs last year comparison question

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

Labels (2)

• ### Set Analysis

1 Solution

Accepted Solutions
Specialist II

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

6 Replies
Specialist II

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

Partner - Creator
Author

But is this really not possible with set expressions?

Partner - Creator
Author

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

``````Data:
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;``````

Specialist II

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:

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

Partner - Creator
Author

Makes sense - thanks for the elaboration! 🙂

Contributor II

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