topic Re: Set Analysis: Last Year Same Week in App Development

Set Analysis: Last Year Same Week

semgem12 — Wed, 02 May 2018 08:04:47 GMT

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

Week	Sales for Week	Sales Last Year
1831	100.00	95.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.

Re: Set Analysis: Last Year Same Week

sunny_talwar — Wed, 02 May 2018 09:13:19 GMT

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

Re: Set Analysis: Last Year Same Week

YoussefBelloum — Wed, 02 May 2018 09:13:57 GMT

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)

Re: Set Analysis: Last Year Same Week

semgem12 — Wed, 02 May 2018 11:43:16 GMT

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).

Re: Set Analysis: Last Year Same Week

YoussefBelloum — Wed, 02 May 2018 11:46:28 GMT

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

one

=sum(Sales)

and another one:

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

Re: Set Analysis: Last Year Same Week

semgem12 — Wed, 02 May 2018 13:19:52 GMT

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.

Re: Set Analysis: Last Year Same Week

semgem12 — Wed, 02 May 2018 13:20:30 GMT

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

Re: Set Analysis: Last Year Same Week

YoussefBelloum — Wed, 02 May 2018 13:22:50 GMT

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

Re: Set Analysis: Last Year Same Week

sunny_talwar — Wed, 02 May 2018 13:25:36 GMT

Something like this?

Re: Set Analysis: Last Year Same Week

menta — Wed, 02 May 2018 14:38:54 GMT

For previous year

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

Re: Set Analysis: Last Year Same Week

semgem12 — Thu, 03 May 2018 13:30:57 GMT

Yes that looks good. Can you tell me how you achieved it please?

Re: Set Analysis: Last Year Same Week

menta — Thu, 03 May 2018 13:55:06 GMT

Normally if you insert the quote you can search like a text. To verify set analysis I don't insert the name of the expression and in the title i check if the set analysis works properly

Re: Set Analysis: Last Year Same Week

sunny_talwar — Thu, 03 May 2018 23:40:39 GMT

I attached the qvf with my response above, did you get a chance to open it and check it out?

Re: Set Analysis: Last Year Same Week

semgem12 — Fri, 04 May 2018 09:03:56 GMT

Thanks Sunny, I didn't spot the attachment. That has worked perfectly, really appreciate it.

For anyone else who might find this useful the script says

Table:
LOAD * INLINE [
Week,Sales
1731,95
1732,97
1733,99
1831,100
1832,101
1833,102
];

AsOfTable:
LOAD DISTINCT Week as AsOfWeek,
Week,
'CY' as Flag
Resident Table;

Concatenate (AsOfTable)
LOAD DISTINCT Week as AsOfWeek,
Week-100 as Week,
'PY' as Flag
Resident Table;

Within the table/chart the dimension is AsOfWeek and the two measures are

Sales for Week: Sum({<Flag = {'CY'}>}Sales)

Sales for Last Year Same Week: Sum({<Flag = {'PY'}>}Sales)