Sum (sales) on a weekly scale but only sum one specific date in that week

[reivax31]

Hi Qlikers,

I created a chart with dimensions MyDate and measure sum(sales). I use alternative dimensions (my week, my month, etc...) table is like :

MyDate	MyWeek	Sales
15/01/2018	3	10
16/01/2018	3	11
17/01/2018	3	12
18/01/2018	3	13
19/01/2018	3	14
20/01/2018	3	15
21/01/2018	4	16
22/01/2018	4	17
23/01/2018	4	18
24/01/2018	4	19
25/01/2018	4	20
26/01/2018	4	21

So right know when I switch my dimension to MyWeek I will have a sum of all my values in that week. I would like to only peek the max date of that week. So for week 3 the value should be 15  and for week 4 value is 21...

Any idea ? thanks for your help

[brunobertels]

Hi

It's more clear then

Assume that in a straight table you do not have Alternative Dimension such as in graph chart

So the Time Dimension must be Week Or Date , but no Alternative dimension are allowed until you use for example QS variable to switch between day and Week

[reivax31]

Hi, Then I will use variables button to swith the dimension and at the same time trigger a different measure. This way I will have also the expected result

[sunny_talwar]

Did you ever get a solution for this

[reivax31 · Accepted solution]

Hi, I found a solution by changing my data base. What I did is creating a data base like this:

MyDate	MyWeek	Sales	MY MESURE
15/01/2018		10	14	-
16/01/2018		11	14	-
17/01/2018		12	14	-
18/01/2018		13	14	-
19/01/2018		14	14
20/01/2018	3	10	14	10
21/01/2018		16	21	-
22/01/2018		17	21	-
23/01/2018		18	21	-
24/01/2018		19	21	-
25/01/2018		20	21	-
26/01/2018	4	21	21	21

During load I will create a field MyWeek where only the last day of the week is field in. This way when I switch my dimension to MyWeek I will only sum(sales) at that specific day.

[sunny_talwar]

Nice... that is great...