Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

reivax31
Contributor III

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

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 :

 

MyDateMyWeekSales
15/01/2018310
16/01/2018311
17/01/2018312
18/01/2018313
19/01/2018314
20/01/2018315
21/01/2018416
22/01/2018417
23/01/2018418
24/01/2018419
25/01/2018420
26/01/2018421

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

1 Solution

Accepted Solutions
reivax31
Contributor III

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

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

MyDateMyWeekSalesMY MESURE
15/01/20181014-
16/01/20181114-
17/01/20181214-
18/01/20181314-
19/01/20181414
20/01/20183101410
21/01/20181621-
22/01/20181721-
23/01/20181821-
24/01/20181921-
25/01/20182021-
26/01/20184212121

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.

24 Replies
MVP
MVP

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

May be this

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

reivax31
Contributor III

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

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
Honored Contributor II

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

try below expression:

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

reivax31
Contributor III

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

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

brunobertels
Valued Contributor

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

Hi

In a straight table

try this :

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

zebhashmi
Valued Contributor

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

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

reivax31
Contributor III

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

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
Contributor III

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

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
Valued Contributor

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

try this

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