Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My table looks like this:
House | Cats | Dogs | % Total of Cats (PTC) | Dog/Cat Ratio (CDR) | WAC |
---|---|---|---|---|---|
- | 45 | 35 | 100% | - | .7711 |
House 1 | 10 | 20 | 22% | 2 | .44 |
House 2 | 15 | 10 | 33% | .67 | .2211 |
House 3 | 20 | 5 | 44% | .25 | .11 |
The calculations I'm using are as follows:% Total Cats (PTC)
House | Cats | Dogs | % Total of Cats (PTC) | Dog/Cat Ratio (DCR) | WAC |
---|---|---|---|---|---|
- | sum([Cats]) | sum([Dogs]) | sum(Sum([Cats]) / (sum(total [Cats]))) | - | **need to figure out how to sum these values and re-use that value** |
[House] | [Cats] | [Dogs] | Sum([Cats]) / (sum(total [Cats])) | [Cats] / [Dogs] | (Sum([Cats]) / (sum(total [Cats]))) * ([Dogs] / [Cats]) |
Read WAC as total percentage of cats times dog/cat ratio.
What I need to do is figure out how to sum the values in the WAC column. I can't get QlikView to do that for me - in other words I can't get QlikView to calculate and show the .7711 value demonstrated above. Not only do I want to sum these values, but I want to re-use the sum value later on in another chart. So later on I want to multiply something by the .7711 value.
I'm sure this seems bizarre and maybe there's even an easy answer that I'm just missing. But I can't figure out how to write the expression to total the WAC values for me. Can someone please help? I sincerely appreciate any guidance you can provide. Thank you!
If you get the expression as it is, does it work?
I guess it depends where you use it, but you can write something like:
sum ( aggr( sum(Dogs)/suM(total Cats), House))
You somehow need to wrap whole thing with a SUM(), then it will sum the column values.
Since you are using SUM() already, SUM() within a SUM() will not work.
Look into AGGR() functions and play with it a little. I am sure that would work.
What would help if you upload a sample document here.
Not sure, if I got you, but I think everything was simpler.
Please, check my app
Also try selecting "sum" for "Total Mode" under Expression tab for the WAC expression.
Please post your feedback.
This got my halfway there. It did make QlikView show the sum of the rows. However now I need to reuse that value elsewhere. Thanks for the ideas thus far - I sincerely appreciate your time and effort trying to help solve this matter.
Hi,
if you want to use this value in the same table try the following formula:
sum(total {<House=>} Dogs)/sum(total {<House=>} Cats)
If you get the expression as it is, does it work?
I guess it depends where you use it, but you can write something like:
sum ( aggr( sum(Dogs)/suM(total Cats), House))
This worked. That you for your tremendous help. I was stuck on this for a long time and can now continue with my project. Thank you so much!
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