topic Re: How to use aggr function in ad-hoc report in QlikView

How to use aggr function in ad-hoc report

Fri, 07 Mar 2014 09:17:43 GMT

Hi all,

I am just beginner in qlikview, I got an issue when create an ad-hoc report. The report works well with all enabled dimensions, but if we adjust the number of dimensions, the cost value not correct anymore. Because we don't have exactly amount for each demo so in each demo we want to show the value of 'All Demo' Value, please refer to attached file for more detail.

Any help will be appreciated much!

Re: How to use aggr function in ad-hoc report

tresB — Fri, 07 Mar 2014 09:38:57 GMT

May be like attached sample?

If not, let know the exact output you are looking for.

Re: How to use aggr function in ad-hoc report

sudeepkm — Fri, 07 Mar 2014 09:42:55 GMT

In your chart currently you are showing the value of All Demo (500000). do you want to show something else. I see that you have zero for all other Demos except All Demos.

Can you please explain me when you say "Adjust the number of dimensions" do you mean adding more dimensions or something else?

Re: How to use aggr function in ad-hoc report

Fri, 07 Mar 2014 11:05:22 GMT

Hi Tresesco & Sudeep,

When user check the client dimension, I want the output like this:

ABC	C1	AD15-25	500000	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
XYZ	C3	AD15-25	500000	55000
XYZ	C3	AD25-35	500000	20000
XYZ	C3	AD35-45	500000	20000
XYZ	C3	AD45-55	500000	12000
XYZ	C3	AD7-15	500000	10000
XYZ	C4	AD15-25	500000	55000
XYZ	C4	AD25-35	500000	20000
XYZ	C4	AD35-45	500000	20000
XYZ	C4	AD45-55	500000	12000
XYZ	C4	AD7-15	500000	10000

When user uncheck the client dimension, 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
XYZ	AD15-25	1000000	110000
XYZ	AD25-35	1000000	40000
XYZ	AD35-45	1000000	40000
XYZ	AD45-55	1000000	24000
XYZ	AD7-15	1000000	20000

I can handle this with an If condition,

If client 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, I cannot cover the selected dimensions in Aggr function. Do we have any idea to solve this?

Re: How to use aggr function in ad-hoc report

Sat, 08 Mar 2014 03:51:34 GMT

Hi Tresesco,

Thanks for your quick reply, but it's not what I mean, Please refer to my below reply for more detail.