Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

cliff_clayman
New Contributor III

Set Analysis for calculating 3 different values

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'?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis for calculating 3 different values

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')

7 Replies
MVP
MVP

Re: Set Analysis for calculating 3 different values

May be like this:

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

cliff_clayman
New Contributor III

Re: Set Analysis for calculating 3 different values

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

neelamsaroha157
Valued Contributor II

Re: Set Analysis for calculating 3 different values

Hey Cliff try this out

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

Let me know if it works..

MVP
MVP

Re: Set Analysis for calculating 3 different values

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')

cliff_clayman
New Contributor III

Re: Set Analysis for calculating 3 different values

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

MVP
MVP

Re: Set Analysis for calculating 3 different values

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')

cliff_clayman
New Contributor III

Re: Set Analysis for calculating 3 different values

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