Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (2)
2 Replies
MVP
MVP

Re: How to use Aggr NODISTINCT in ad-hoc report

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

Re: How to use Aggr NODISTINCT in ad-hoc report

Hi Swuehl,

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

Community Browser