Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a chart like
Currently my coll% is showing coll/Setup but what i want is that the coll amount should always be divided by the first setup only, i.e 40/100,20/100,10/100 giving me 40%,20%,10%. This should be respective of each and every month. Basically i want to take the setup amount where my setup month is same as my collection month.
Is there a way to this?
I think this is the Coll% expression you are looking for
sum(CollectionAmount)/sum({<flag = {1}>} total <SetupMonth> SetupAmount)
Note the flag for SetupMonth = Nov is set for CollectionMonth = Nov (90). perhaps the flag should be Jan (110). The expression is interpreting the value correctly according to the flag.
You may want to create a derived flag field in the load script to indicate the correct setup field. If you need more specific help, I suggest that you upload a small sample qvw with some representative sample data.
Hi,
Please find the attachments above. Let me know if you need anything else. Yes, i have created a flag but im not sure how to use it.
Thanks!
Try like:
sum(CollectionAmount)/Above(sum(SetupAmount), RowNo()-1,0)
Hi,
Try with:
Sum(CollectionAmount)/Max(TOTAL <SetupMonth> {<flag = 1>}SetupAmount)
Saludos
I think this is the Coll% expression you are looking for
sum(CollectionAmount)/sum({<flag = {1}>} total <SetupMonth> SetupAmount)
Note the flag for SetupMonth = Nov is set for CollectionMonth = Nov (90). perhaps the flag should be Jan (110). The expression is interpreting the value correctly according to the flag.
It wont work if i change my view
Its working just fine. Dont worry about the Nov to Jan issue i will handle that. One more small help. I want all those setup amount with flag 1 to be shown beside the respective months as dimension so as to make it easy for the users to read the data. Can you please help me with that?
Its just that i dont want to confuse the users with multiple setup amounts
Just use the denominator of the expression -
sum({<flag = {1}>} total <SetupMonth> SetupAmount)
I suggest that you do it as an expression rather than a dimension, so you can avoid the aggr(). Drag it into the desired position in the pivot.