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

[Anonymous]

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

[Gysbert_Wassenaar]

Please post a small qlikview document that demonstrates the problem.

[Anonymous]

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

[Gysbert_Wassenaar]

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.

[Anonymous]

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

[Gysbert_Wassenaar]

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

[Anonymous]

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

[Gysbert_Wassenaar]

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.