topic Re: Current week v.s. different week previous year comparison (same number of days) in QlikView

Current week v.s. different week previous year comparison (same number of days)

Anonymous — Sun, 05 Jun 2016 08:53:30 GMT

Hi all,

I would like to show sales data for each store comparing one week to a different week in the previous year, provided that the end user can select weeks he want to compare.

My model is quite simple, main fact table and two calendars (islands):

What I'm not able to implement is the following part of the requirement: if the first week selected is the current one, i.e. only monday to tuesday data available since today is wednesday, I need to show sales for the other week up to tuesday and not the whole 7 days.

Moreover, there can be situations in which for store A I have monday and tuesday data, for store B only monday

I've tried with set analysis but I'm stuck on the point where sets are calculated once per chart, so I cannot show someting like this

Store	Sale No. Current Week	Sales No. Previous Week
A	2	2
B	1	1

Any ideas?

Best regards,

Giuseppe

Re: Current week v.s. different week previous year comparison (same number of days)

Gysbert_Wassenaar — Sun, 05 Jun 2016 14:40:16 GMT

Please post a small qlikview document that demonstrates the problem.

Re: Current week v.s. different week previous year comparison (same number of days)

Anonymous — Mon, 06 Jun 2016 21:53:52 GMT

Please find qvw file and source xls atteched.

If you take a look at the picture below, you'll se that in chart "Sales", 2015 column shows 7 for both "A" and "B" shop while I need to get 5 and 4 respectively.

This is because I can't find a way, provided that it's not so easy to modify source data (very large data set with consolidated data I cannot reload from legacy systems), to complete the set analysis

=count({$<WeekFull={"$(settConf)"}>} Date) to take into account that number of days of compare week should be different for dimension Shop.

Regards and thanks for your support.

Giuseppe

Re: Current week v.s. different week previous year comparison (same number of days)

Gysbert_Wassenaar — Tue, 07 Jun 2016 14:15:27 GMT

If you take a look at the picture below, you'll se that in chart "Sales", 2015 column shows 7 for both "A" and "B" shop while I need to get 5 and 4 respectively.

Why? Your data contains 7 records for the week 22 - 25/05/2015 for A and also 7 records for B. The expression returns the correct result.

Re: Current week v.s. different week previous year comparison (same number of days)

Anonymous — Tue, 07 Jun 2016 14:45:00 GMT

Because business users would like to compare sales amount for an even number of days, so if there are only four days of sales in analysis week, I need to show te amount of sales of four days in the comparions week.

Regards,

Giuseppe

Re: Current week v.s. different week previous year comparison (same number of days)

Gysbert_Wassenaar — Tue, 07 Jun 2016 15:17:29 GMT

Then don't select a week, but select the days.

Re: Current week v.s. different week previous year comparison (same number of days)

Anonymous — Tue, 07 Jun 2016 15:45:20 GMT

You're partially right but this is not feasible: business users select one week, all the "magic" needs to happen in background.

Partially beacuse selecting days is not enough since, number of sales days is related to single shop.

I know that set analysis is only evaluated once per single chart, just wanted to be sure that some nice trick I'm not aware of would do the trick.

Regards,

Giuseppe

Re: Current week v.s. different week previous year comparison (same number of days)

Gysbert_Wassenaar — Wed, 08 Jun 2016 16:28:05 GMT

I don't even understand exactly what the 'magic' should do. If you select two arbitrary weeks and one week may have 4 days and the other 2 days then which days need to be used in each week?

I see only one (easy) way out: let the user select the two weeks and then let him/her select the days that should be compared.