topic Re: Set Analysis % Calculation in QlikView

Set Analysis % Calculation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Below is my calculation to get the amount of money by pay type for a range.

Sum({<[Amt Paid] ={">0<2500"}>}[Amt Paid])

What I would like to do is now calculate the percent on that range so check would be about 54/56%, I thought it was as simple as " / sum([Amt Paid])" however that just takes the over all total? not just the summed section. Do i need to write another analysis set to capture and divide the total?

Thank you in advance for any assistance anyone can provide

Re: Set Analysis % Calculation

sunny_talwar — Mon, 07 May 2018 13:49:59 GMT

May be this

Sum({<[Amt Paid] ={">0<2500"}>}[Amt Paid])/Sum(TOTAL {<[Amt Paid] ={">0<2500"}>}[Amt Paid])

Re: Set Analysis % Calculation

Anonymous — Mon, 07 May 2018 13:54:40 GMT

So I did need to do another set analysis to capture the total separately, thank you.

Re: Set Analysis % Calculation

sunny_talwar — Mon, 07 May 2018 13:59:20 GMT

If the ranges are predefined, I would create them in the script and then instead of creating them as multiple expressions, I would just create them as one expression with 2 dimensions.

Re: Set Analysis % Calculation

Anonymous — Mon, 07 May 2018 14:13:44 GMT

the ranged are unfortunately not predefined, i set the ranges in the expression. Should I still build the calculation in the data load script as a best practice?

Re: Set Analysis % Calculation

sunny_talwar — Mon, 07 May 2018 14:15:53 GMT

But you are hard-coding these ranges in your expressions, right? or are they decided based on some input from your users?

Re: Set Analysis % Calculation

Anonymous — Mon, 07 May 2018 14:31:15 GMT

Correct, I am hard coding them.

Re: Set Analysis % Calculation

sunny_talwar — Mon, 07 May 2018 14:37:38 GMT

So, you can hard code them in the script then (assuming Amt Paid will always be a positive number, you can do like this)

If([Amt Paid] <= 2500, Dual('Transactions of $2,500 or less', 1),

If([Amt Paid] <= 10000, Dual('Transactions $2,501 to $10,000', 2),

If([Amt Paid] <= 100000, Dual('Transactions $10,001 to $100,000', 3),

....

))) as Transaction_Range

and then in your chart

Dimensions

Payment Method

Transaction_Range

Expression

Sum([Amt Paid])/Sum(TOTAL <Transaction_Range> [Amt Paid])

Re: Set Analysis % Calculation

Anonymous — Mon, 07 May 2018 14:50:28 GMT

I didnt think of that, thank you, I will give it a go.

Many Thank You