Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Yet another this period vs last year comparison question

Hi all,

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

datestationproductvolume
2020-04-03
1001300
2020-04-02
1001200
2020-04-01
1001100
2019-04-03
100170
2019-04-02
100180
2019-04-01
100190

 

I wish to create this straight table:

Screenshot 2020-04-14 at 15.36.57.png

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)
1 Solution

Accepted Solutions
Specialist II
Specialist II

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

View solution in original post

6 Replies
Specialist II
Specialist II

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

View solution in original post

Partner
Partner

Thanks for your reply - works as expected. 🙂

But is this really not possible with set expressions?

Partner
Partner

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;

 

Specialist II
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:

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

Partner
Partner

Makes sense - thanks for the elaboration! 🙂

Contributor II
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-DayThis Year 2020Last Year 2019
04-0130070
04-0220080
04-0310090