Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
this one returns blank chart. I tried some variant but I didn't get it
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
=Max(aggr(Sales,MyDate))
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
Hi, itreturns blank value in my chart ...
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 |
Why you need to display the same value for all dates within the week ?
Do you also need its total ?
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 |
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
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.