Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to calculate a percentage value within a pivot tabel.
We have done this before by creating a variable containing the total value and used it as the divisor in the expression.
But maybe there is a better way to do this.
My Pivot Table should look similar to this one (right now only the exp1 columns are filled):
Month | Jan | Feb | ||||
KPI | Exp1 | Exp2 | % | Exp1 | Exp2 | % |
KPI1 | 10 | 60 | 16,6666667 | 20 | 90 | 22,2222222 |
KPI2 | 20 | 60 | 33,3333333 | 30 | 90 | 33,3333333 |
KPI3 | 30 | 60 | 50 | 40 | 90 | 44,4444444 |
Total | 60 | 100 | 90 | 100 |
How can I calculate the Exp2 and % columns in QlikView?
I have tried to use this formular:
=SUM([Values]) / SUM(Total [Values])
But if I do so I will get the same values for all Exp2 columns and not with reference to the month dimension.
That means if I make a "Total" all dimensions will be ignored.
How can I ignore the KPI column on the first hand and reference to the month column on the other hand?
Any suggestions?
Thanks in advance
Simon
OK I found the solution
The formular must be:
SUM([Values]) / SUM(Total <[Month]> [Values])
It is also possible to do a set analysis with this:
SUM({<[Country]={'DE'}>}[Values]) / SUM({<[Country]={'DE'}>}TOTAL <[Month]> [Values])
This will limit the country field to Germany and will make a total over all dimension in chart except the month.
Hi Simon,
Please use the below for your 2nd expression
Sum(Values) / aggr(NODISTINCT sum(Values),Month)
Regards,
Sajeevan
Hi,
i think you can solve my problem again.
i've dimemsions dim1,dim2,dim 3..in hierarchial format as in pivot table.
i've a value column in the table, as one expr....i'm showing sum(value) and i also have dimension due date....& if the due date <today() date then in the expr column Overdue Amt i simply show sum(value) else i'll show it the Not due column...this is fine....
now i've to find %Overdue & %Not due.....wich is Overdue Amt/sum(value) & Not Due Amt/sum(value) respectively...
i'm getting the % fine..for each row...but at the total level i dnt want to see sum of all % for Overdue & %Not Due
Bt only the sum of Overdue Amt/sum of Remainin Amt & same for %Not due as well
Plz help
stuck for 1 day cmplete
Regards
Can you attach sample QV file?