topic Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart in QlikView

How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

Fri, 15 Apr 2016 17:28:38 GMT

Hi,

We have a pivot table where we have 4 attributes and some metrics. In this metric, we have one which is called SLOPE. This slope metric should rollup to higher levels in pivot differently i.e. should not just be sum up of lower levels. Something similar how we achieve using SCOPE function in SSAS.

Please see below screen shot

Please let me know how can I achieve this functionality in QlikView 11 pivot chart.

Any help is highly appreciated.

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

swuehl — Fri, 15 Apr 2016 18:01:24 GMT

You can use Dimensionality() resp.SecondaryDimensionality() to check on which dimensional level you are aggregating:

=Pick(Dimensionality()+1,

TotalLevelExp,

Dim1LevelExp,

Dim2LevelExp, // e.g. Sum(Aggr( Sum(Slope)*2, Country, State, City, Area))*3

Dim3LevelExp, //e.g. Sum(Slope)*2

DetailLevelExp //e.g.Sum(Slope)

)

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

Fri, 15 Apr 2016 18:20:12 GMT

I am sorry I am still learning the Qlik so want some clarification....

Dimensionality+1 --- what does it do? Do I need to use this one or SecondaryDimensionality() function

and what does SecondaryDimensionality() do?

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

sunny_talwar — Fri, 15 Apr 2016 18:25:23 GMT

You can read about Dimensionality() and SecondaryDimensionality() here:

How to use - Dimensionality()

The second dimension... or how to use secondarydimensionality()

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

swuehl — Fri, 15 Apr 2016 18:33:23 GMT

You can also hit F1 in the desktop client and search the HELP, the Help file is more often right than wrong:

Examples of Chart Inter Record functions ‒ QlikView

Dimensionality ‒ QlikView

Secondarydimensionality ‒ QlikView

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

Fri, 15 Apr 2016 18:45:46 GMT

Thank you. I shall check this and revert

Re: How to achieve SCOPE() like functionality or customized Rollup values in Pivot chart

Thu, 21 Apr 2016 05:08:23 GMT

Thank you swuehl. You answer helped us achieving what we were looking for.