24 Replies Latest reply: Jan 29, 2018 8:12 AM by Sunny Talwar

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 :

 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

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

• 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).

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

Did you ever get a solution for this

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

• 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

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

• 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?

• 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

• 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.

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

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

Hi,

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

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

=Max(aggr(Sales,MyDate))

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

Hi, itreturns blank value in my chart ...

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

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

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

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

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

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
• Re: Sum (sales) on a weekly scale but only sum one specific date in that week

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
• Re: Sum (sales) on a weekly scale but only sum one specific date in that week

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

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

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

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

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

Do you also need its total ?

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

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

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

MyWeek MyDate 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 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 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.

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

Nice... that is great...