Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikview gurus!
In a pivot table, I'm trying to catch the 1st dimension field name and and display it in the second one.
Dim1 | dim2 | Count |
A | C (Dim1) | 1 |
D(Dim1) | 10 | |
B | E (Dim1) | 100 |
F (Dim1) | 1000 |
Why do I need that?
I need to display that AND being able to slap dynamically the column names ("Allow pivoting" ticked).
Dim1 | dim2 | Count |
A | C(9%) | 1 |
D(31%) | 10 | |
B | E (6.25%) | 200 |
F (93.75%) | 3000 |
ie E = 100/(200+3000)*200
I Can get the calculation right in static version but no way to get the "higher" dimension name dynamically.
The Dim2 formula for the static version:
=Dim2 & ' (' &
num(
aggr(NODISTINCT count(DISTINCT Metric), Dim1, Dim2 )/
aggr(NODISTINCT count(DISTINCT Metric), Dim1)
,'##0.00%')
&')'
Any thought?
If this what you are looking for?
Count(DISTINCT Metric) / Count(DISTINCT TOTAL <Dim1> Metric)
- assuming Metric is a field - add the formatting (Num()) and text prefix as required.
Hi Jonathan.
Thanks a lot for answering.
My main problem is to get Dim1 name dynamically. The calculation is working if i keep DIM1 name as a field but I cannot get it from a variable
Perhaps this then:
=Count(DISTINCT Metric) / Count(DISTINCT TOTAL <$(vDynamicDim1)> Metric)
Rename to the correct variable name.