Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

giuseppe_uleri
New Contributor

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

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

model.JPG

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

StoreSale No. Current WeekSales No. Previous Week
A22
B11

Any ideas?

Best regards,

Giuseppe

7 Replies

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

Please post a small qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
giuseppe_uleri
New Contributor

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

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.

sales.JPG !

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)

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.


talk is cheap, supply exceeds demand
giuseppe_uleri
New Contributor

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

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)

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


talk is cheap, supply exceeds demand
giuseppe_uleri
New Contributor

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

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)

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.


talk is cheap, supply exceeds demand
Community Browser