Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
)
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)
)
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?
You can read about Dimensionality() and SecondaryDimensionality() here:
The second dimension... or how to use secondarydimensionality()
You can also hit F1 in the desktop client and search the HELP, the Help file is more often right than wrong:
Thank you. I shall check this and revert
Thank you swuehl. You answer helped us achieving what we were looking for.