Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Example I have a data like this.
LOAD * INLINE [
Network, Client, Demo, Cost, IMP
ABC, C1, AD7-15, 0, 10000
ABC, C1, AD15-25, 0, 55000
ABC, C1, AD25-35, 0, 20000
ABC, C1, AD35-45, 0, 20000
ABC, C1, AD45-55, 0, 12000
ABC, C1, All Demo, 700000, 117000
ABC, C2, AD7-15, 0, 10000
ABC, C2, AD15-25, 0, 55000
ABC, C2, AD25-35, 0, 20000
ABC, C2, AD35-45, 0, 20000
ABC, C2, AD45-55, 0, 12000
ABC, C2, All Demo, 500000, 117000
];
Because we don't have exactly cost amount for each demo so in each demo of client we want to show the value of 'All Demo' Value. I create an adhoc report for user, when user select client dimension, I want the output like this:
Network Client Demo Cost IMP
ABC | C1 | AD15-25 | 700000 | 55000 |
ABC | C1 | AD25-35 | 500000 | 20000 |
ABC | C1 | AD35-45 | 500000 | 20000 |
ABC | C1 | AD45-55 | 500000 | 12000 |
ABC | C1 | AD7-15 | 500000 | 10000 |
ABC | C2 | AD15-25 | 500000 | 55000 |
ABC | C2 | AD25-35 | 500000 | 20000 |
ABC | C2 | AD35-45 | 500000 | 20000 |
ABC | C2 | AD45-55 | 500000 | 12000 |
ABC | C2 | AD7-15 | 500000 | 10000 |
or dis-select, I want the out put like below:
ABC | AD15-25 | 1000000 | 110000 |
ABC | AD25-35 | 1000000 | 40000 |
ABC | AD35-45 | 1000000 | 40000 |
ABC | AD45-55 | 1000000 | 24000 |
ABC | AD7-15 | 1000000 | 20000 |
I can handle this with an If condition,
If Client dimension is checked then:
Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network, Client)
else
Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network)
But in reality, there are more than 10 dimensions for user to select not only client and network, I cannot cover the selected dimensions in Aggr function. Do we have any idea to solve this?
Please refer to attached file for more detail
What about changing your data model a bit, loading the all demo cost per client in a separate linked table, then let QV do the aggregation on the right level?
Hi Swuehl,
Thanks for your suggestion, I will try and feedback to you soon