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: 
semgem12
Contributor III
Contributor III

Set Analysis: Last Year Same Week

Hello

Hope someone can help with this please.

I have a table with week numbers and sales for those weeks. The weeks take the format yyww, e.g. 1831 means week 31 of year 2018.

Within a Qlik Sense sheet I want to be able to show each week and the sales for that week as well as the sales for the same week in the previous year. E.g. an entry for 1831 might show

WeekSales for WeekSales Last Year
1831100.0095.00

meaning sales in 1831 were 100.00 and in 1731 they were 95.00.

This is just a sample of the data

LOAD * INLINE [

Week,Sales

1731,95

1732,97

1733,99

1831,100

1832,101

1833,102

etc.

];

So in a table the dimension would be Week and the Sales for Week would be sum(Sales).

I'm not sure how to get the sales for the previous year. I want to be able to do something like

sum({$<Week = {Week-100}>}Sales)

But I know I can't do this.

Can anyone think of a way to do this please?

Thanks.

1 Solution

Accepted Solutions
sunny_talwar

Something like this?

Capture.PNG

View solution in original post

13 Replies
sunny_talwar

I suggest using an The As-Of Table here to associate this yearweek to last yearweek

YoussefBelloum
Champion
Champion

Hi,

there is a lot of methods to get the week of the last year, but with the data you have, what you proposed "sum({$<Week = {Week-100}>}Sales)" is interesting, it is just the syntax that is incorrect..


try this expression:


=sum({$<Week = {$(=Max(Week)-100)}>}Sales)

semgem12
Contributor III
Contributor III
Author

Thanks Youssef. Unfortunately that did not work. The maximum week in my data set is 1852 and the expression aggregated all the sales into week 1752 (i.e. 1852-100).

YoussefBelloum
Champion
Champion

it is normal, you will need one expression per year:

one

=sum(Sales)


and another one:

=sum({$<Week = {$(=Max(Week)-100)}>}Sales)

semgem12
Contributor III
Contributor III
Author

Thanks but not sure I understand.....

sum({$<Week = {$(=Max(Week)-100)}>}Sales)

generates results like this


Week    LastYearSales
.

.

1750              0.00

1751              0.00  

1752          999.99

1801              0.00

1802              0.00

1803              0.00

.

.

This is because my weeks go up to 1852 and so max(week)-100 = 1752. So only 1752 has a value. Each week should show its last year's value.

semgem12
Contributor III
Contributor III
Author

Thanks. I've read through the article but not sure how to apply and use it in this situation.

YoussefBelloum
Champion
Champion

I see, for this you should use above() function or the solution provided by stalwar1‌ above.

sunny_talwar

Something like this?

Capture.PNG

menta
Partner - Creator II
Partner - Creator II

For previous year


sum({$<Week = {"$(=Max(Week)-100)"}>}Sales)