Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use Aggr NODISTINCT in ad-hoc report

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

ABCC1AD15-2570000055000
ABCC1AD25-3550000020000
ABCC1AD35-4550000020000
ABCC1AD45-5550000012000
ABCC1AD7-1550000010000
ABCC2AD15-2550000055000
ABCC2AD25-3550000020000
ABCC2AD35-4550000020000
ABCC2AD45-5550000012000
ABCC2AD7-1550000010000

or dis-select, I want the out put like below:

ABCAD15-251000000110000
ABCAD25-35100000040000
ABCAD35-45100000040000
ABCAD45-55100000024000
ABCAD7-15100000020000

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

2 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

Hi Swuehl,

Thanks for your suggestion, I will try and feedback to you soon