topic Passing the first dimension to Aggr function or Total function in set in pivot chart ? in QlikView

Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Thu, 07 Jan 2016 09:22:43 GMT

Hii folks,

Suppose i am having 3 dimensions in a pivot chart as zone,region,branch and i m show sum(sales) as expression.

Now i want to add another expression - something like

aggr(NODISTINCT sum(Sales),(FirstDimension))

sum(TOTAL<FirstDimension> Sales)

i.e if Zone is the first dimension of the chart output should be zone wise totals , if users drags the dimension and makes region as the first dimension in the chart then output should be Regionwise totals.

Is this possible ?

Any help would be appreciated.

Thanks,

Rohan

jagan‌

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

sunny_talwar — Thu, 07 Jan 2016 09:55:35 GMT

I don't think this would be possible. A not so efficient work around would be to have overlapping charts which hide show based on button may be where each chart have one of the dimension as the first dimension.

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Anonymous — Thu, 07 Jan 2016 10:09:02 GMT

Agreed with you Sunny...

But one thing, If this scenario comes in Adhoc Reporting, where user will be picking the dimension you dont think using variables and FieldSelection combination and we can make it dynamic.

I mean to say, I am putting the First selected value of dimension into a variable and then using that variable in grouping..

Is this not possible?

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

sunny_talwar — Thu, 07 Jan 2016 10:10:22 GMT

It might work. Will have to be tested out.

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Thu, 07 Jan 2016 10:15:46 GMT

I am having Adhoc reporting itself , and in the scenario mentioned by you what if a user selects more than 1 dimension in adhoc , in such case how will qlikview identify which is the first selected Dimension that should be passed to the variable ?

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Anonymous — Thu, 07 Jan 2016 10:28:16 GMT

I think, this is what Rohan is asking..

As per him

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Thu, 07 Jan 2016 10:32:06 GMT

yes u are correct balraj.

What i want is something like -

aggr(NODISTINCT sum(Sales),getobjectfield(0)))

-GetObjetcfield(0) gives me the name of my first dimension,however i am not able to pass it to AGGR fuction as a dimension.

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

sunny_talwar — Thu, 07 Jan 2016 10:34:44 GMT

I guess you were right. It can work. Try it out Rohan Rane‌

Selection in vDim1 will drive the output of the second expression here (=Sum(TOTAL <$(vDim1)>Value))

Thanks balrajahlawat‌ for forcing me to reconsider.

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Anonymous — Thu, 07 Jan 2016 10:38:56 GMT

Did not force you. Just shared my thoughts with you

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

sunny_talwar — Thu, 07 Jan 2016 10:39:43 GMT

Your thoughts forced me to reconsider , how about that?

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Anonymous — Thu, 07 Jan 2016 10:41:34 GMT

Thats right

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Tue, 12 Jan 2016 12:33:19 GMT

Hello both,

i finally found a dynamic solution to this requirement without the use of input box or variables -

sum(TOTAL<$(=GetObjectField(0,'CH24'))> Sales)

aggr(NODISTINCT sum(Sales),$(=GetObjectField(0,'CH24')))

Thank you for your solutions as well.

Regards,

Rohan

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

sunny_talwar — Tue, 12 Jan 2016 16:58:00 GMT

Thanks for sharing that Rohan. This is the first time I am seeing that function. I think this is going to be a very useful function.

Best,

Sunny

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Wed, 13 Jan 2016 04:52:58 GMT

Your Welcome,

Regards,

Rohan

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

rajeshwar1 — Tue, 08 Aug 2023 14:24:44 GMT

Hi,

I tried but it's not working in AD-Hoc pivot.

Re: Passing the first dimension to Aggr function or Total function in set in pivot chart ?

Fran_by — Mon, 22 Apr 2024 10:08:59 GMT

If dimension name has blank symbols , try like this:

sum(TOTAL<"$(=GetObjectDimension(0))")> Sales)

QS give error in expression, but work!