Yet another this period vs last year comparison question
Consider this data model (I have attached the Qlik Sense file as well):
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
Also fiddle around with TOTAL and p(), but not entirely understanding how to solve this.
Re: Yet another this period vs last year comparison question
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