Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is Pivot Table 1
Count of Dinosaur Type | 1800 | 1900 |
Saurischia | 1 | 1 |
Ornithischia | 3 |
|
Grand Total | 4 | 1 |
It is based on Table A below
Dinosaur Type | Dinosaur Name | Century Named | Instances |
Saurischia | Tyrannosaurus Rex | 1900 | 2,500 |
Saurischia | Allosaurus | 1800 | 1,300 |
Ornithischia | Triceratops | 1800 | 3,250 |
Ornithischia | Tyrannosauridae | 1800 | 987 |
Ornithischia | Hadrosauridae | 1800 | 533 |
Using the same Table A above I want to create Pivot Table 2 so it looks like this
Dinosaur Type | Percentage |
Saurischia | 40% |
Ornithischia | 60% |
If I were able to create measures based on Pivot Table 1 to create Pivot Table 2 they would look like this (not Qlik Syntax).
Sum(Saurischia)/Count (Century Named) / (Sum(Saurischia + Ornithischia)/Count (Century Named))
Sum(Ornithischia)/Count (Century Named) / (Sum(Saurischia + Ornithischia)/Count (Century Named))
However, I am looking for one measure based on Table A that will create Pivot Table 2. Can you help?
May be like this:
Sum({<[Dinosaur Type] = {"Saurischia"}>}Instances)/
Count({<[Dinosaur Type] = {"Saurischia"}>}[Century Named])
Sum({<[Dinosaur Type] = {"Ornithischia"}>}Instances)/
Count({<[Dinosaur Type] = {"Ornithischia"}>}[Century Named])
Hi Bala Bhaskar, I updated the question to make it clearer
For your first Pivot you have an expression:
Count([Dinosaur Type])
For your second Pivot you can calculate the percentages directly like this:
Count([Dinosaur Type]) / Count(TOTAL [Dinosaur Type])
And then select number format as %
In Qlik Sense or QlikView you can't create expression based on results from other charts/tables.