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

[#1]

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))

or

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‌

[Anonymous]

Thats right

[#3 · Accepted solution]

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)

or

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

Thank you for your solutions as well.

Regards,

Rohan

[sunny_talwar]

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

[#5]

Your Welcome,

Regards,

Rohan

[rajeshwar1]

Hi,

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

[Fran_by]

If dimension name has blank symbols , try like this:

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

QS give error in expression, but work!