topic Re: Set Analysis for calculating 3 different values in QlikView

Set Analysis for calculating 3 different values

cliff_clayman — Thu, 15 Sep 2016 21:01:58 GMT

I am trying to get a total cost of purchases by type. I have 3 types. Here is the expression that works to get the total for just one type.

=Money(Count(DISTINCT If(FY=vCFY and [PurchaseType] = 'ABC', [PO]))*vPOCostABC,'$#,##0')

How can I change this to Set Analysis and account for two more PurchaseType values of 'DEF' and 'XYZ'?

Re: Set Analysis for calculating 3 different values

sunny_talwar — Thu, 15 Sep 2016 21:06:21 GMT

May be like this:

=Money(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) * vPOCostABC,'$#,##0')

Re: Set Analysis for calculating 3 different values

cliff_clayman — Thu, 15 Sep 2016 21:09:01 GMT

I failed to mention that there are 3 Cost variables as well that go along with each PurchaseType. Each PurchaseType value has a different cost associated with it.

PurchaseType Cost Variable

ABC vPOCostABC

DEF vPOCostDEF

XYZ vPOCostXYZ

Re: Set Analysis for calculating 3 different values

neelamsaroha157 — Thu, 15 Sep 2016 21:10:09 GMT

Hey Cliff try this out

Count(Distinct {<FY = {'$(vCFY)'}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]*vPOCost)

Let me know if it works..

Re: Set Analysis for calculating 3 different values

sunny_talwar — Thu, 15 Sep 2016 21:12:19 GMT

Is Purchase Type one of the dimensions? If it is, then you can try this

=Money(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) *

Pick(Match([PurchaseType], 'ABC', 'DEF', 'XYZ'), vPOCostABC, vPOCostDEF, vPOCostXYZ)

,'$#,##0')

Re: Set Analysis for calculating 3 different values

cliff_clayman — Thu, 15 Sep 2016 21:13:38 GMT

PurchaseType is not a dimension. This expression is just in a text box.

Re: Set Analysis for calculating 3 different values

sunny_talwar — Thu, 15 Sep 2016 21:17:17 GMT

Then may be this:

=Money(Sum(Aggr(Count(DISTINCT {<FY = {$(=vCFY)}, [PurchaseType] = {'ABC', 'DEF', 'XYZ'}>} [PO]) *

Pick(Match([PurchaseType], 'ABC', 'DEF', 'XYZ'), vPOCostABC, vPOCostDEF, vPOCostXYZ), PurchaseType))

,'$#,##0')

Re: Set Analysis for calculating 3 different values

cliff_clayman — Thu, 15 Sep 2016 21:22:22 GMT

That did it...thanks once again!!!