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

[sunny_talwar]

May be this

If(MyDate = Max(MyDate), Sum(Sales))

[reivax31]

Hi Sunny,

I tried this but does work. is it possible that this is due to the fact that all my conversion (MyWeek, MyYear, etc...) are in a correspondance table (key is MyDate).

[agigliotti]

try below expression:

if( MyDate = Max( total <MyWeek> MyDate ), Sales )

[reivax31]

I'm trying this with some variants but still did get the expected result. I will try harder

[brunobertels]

Hi

In a straight table

try this :

max(aggr(sum(aggr(sum(Sales),MyDate,MyWeek)),MyDate))

[zebhashmi]

Do You have a Idea, why Max(MyDate) gives a problem?

[reivax31]

Hi Bruno,

this works nearly perfectly :max(aggr(sum(aggr(sum(Sales),MyDate,MyWeek)),MyDate))

So when I switch my dimensions (date/week) I have te expected result at the little exepction that it does show the value of sales at the max date but the max sales in the week. But at least I do not have a blank chart as I had with other expressions.

[reivax31]

for exemple I use this ? If(MyDate = Max(MyDate), Sum(Sales))

then the max date for each date is it's own value so it doesn't return any value, because max() only work if there is more than one value)

in this expression: if( MyDate = Max( total <MyWeek> MyDate ), Sales )

when I select Weeks it show the good value but if I swith back my dimession to Mydate I only have value for max Mydate. I would like to see all value when dimession is Mydate and only max(mydate) for each week when dimension is Myweek

[zebhashmi]

try this

=Max(aggr(aggr(Sales,MyDate,MyWeek),Sales))