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

[reivax31]

Hi,

this one returns blank chart. I tried some variant but I didn't get it

[brunobertels]

Hi

What is your desired outpout exactly

Wich dimension ? Date or Week , or both ?

See below that in a simple table both last expression proposed works fine with Dimension MyWeek

[zebhashmi]

=Max(aggr(Sales,MyDate))

[reivax31]

Hi,

Yes I want to make it work with both (MyDate, MyWeek).

for this one it work: max(aggr(sum(aggr(sum(Sales),MyDate,MyWeek)),MyDate)) but only if the last day of the week has the highest Sales Value. In the table change the sales value to something lower than 14 for 20/01/2018. ex:

20/01/2018

3

10

for Max(aggr(aggr(Sales,MyDate,MyWeek),Sales)) I really just have blank returned. (maybe because MyWeek is in correspondance table) it not a flat data but a calendar table that converts Dates in orther forms like week, month, year ,etc

[reivax31]

Hi, itreturns blank value in my chart ...

[brunobertels]

Hi

Ok Xavier

But what is your desired output finally :

Ex Do you want somethink like MyMesure 1 or MyMesure 2

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

[agigliotti]

Why you need to display the same value for all dates within the week ?

Do you also need its total ?

[reivax31]

Sorry maybe I didn't explain well what I'm trying to do:

MY MESURE should be one and only measure that gets me the result bellow if I use MyDate or MyWeek. (the purpose is to use alternative dimension so the user can swicth between a day or week view, so when user select a week view I only want to show the last value of the week)

MyDate	sum(Sales)	MY MESURE
15/01/2018	10	10
16/01/2018	11	11
17/01/2018	12	12
18/01/2018	13	13
19/01/2018	14	14
20/01/2018	10	10
21/01/2018	16	16
22/01/2018	17	17
23/01/2018	18	18
24/01/2018	19	19
25/01/2018	20	20
26/01/2018	21	21
MyWeek	Sum(Sales)	MY MESURE
3	70	10
4	111	21

[reivax31]

Ok, imagine on monday a certain Item had the value 10, then on tuesday 11, ... and at the end of the week 10. If user select a day view (MyDate) then he can see the evolution of the item value day after day. But if user swith to a week view because he wants to see week after week the evolution of that item (not sum all the value within the week and also not doing an average that could be easely done). So the rule is that the last value of the week is the value that is the reference, even if during the week the value of the item may have changed

[agigliotti]

take a look at https://community.qlikview.com/thread/258160

I'd suggest you to follow that thread because there is no way to detect the active dimension.