topic Re: Set Analysis Expression <= $0 in QlikView

Set Analysis Expression <= $0

NickHoff — Mon, 27 Mar 2017 20:21:17 GMT

Hello,

Inside a pivot table I have an expression Cost Per Case

SUM(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk)

a variable eCostBenchmark to calculate Benchmark depending on the Org Option selected

Which is defined as:

=@CostBenchmark

Derived from the following INLINE table:

CostLevel:

LOAD * INLINE [

@CostLevelID, @CostLevel, @CostDescriptionField, @CostBenchmark, @OrgLevel

1, HCA, HCACostDescription, BMHCA50PCT, CorporateName

2, Group, GroupCostDescription, BMGroup50PCT, GroupName

3, Division, DivisionCostDescription, BMDivision50PCT, DivisionName

4, Market, MarketCostDescription, BMMarket50PCT, MarketName,

5, Facility, FacilityCostDescription, BMFacility50PCT, Facility

];

Finally an expression to get the variance between [Benchmark] - [Cost Per Case].

I only want my chart to display expressions where the variance between [Benchmark] - [Cost Per Case] <= 0.

I've tried the following with no success:

SUM({<ProcedureSurgeonName = {"=SUM(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk) - $(eBenchmarkCost) <= 0"}>}SupplyChainLUOMTotalCost)/COUNT({<ProcedureSurgeonName = {"=SUM(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk) - $(eBenchmarkCost) <= 0"}>}DISTINCT SurgicalCaseSk)

Re: Set Analysis Expression <= $0

sunny_talwar — Tue, 28 Mar 2017 02:31:52 GMT

Would you be able to share a sample to take a look at this?

Re: Set Analysis Expression <= $0

sunny_talwar — Tue, 28 Mar 2017 18:14:41 GMT

One way to use if statement would be to create a new field from all your dimensions in the table

AutoNumber(GroupName, DivisionName, MarketName, Facility, ProcedureSurgeonName, ProcGroupDesc) as Key

and then use this

SUM({<Key = {"=Sum(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk) >= $(eCostBenchmark)"}>}SupplyChainLUOMTotalCost)/COUNT({<Key = {"=Sum(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk) >= $(eCostBenchmark)"}>} DISTINCT SurgicalCaseSk)

Do this for your variable expression as well and see if that works

Re: Set Analysis Expression <= $0

NickHoff — Tue, 28 Mar 2017 19:24:42 GMT

Sunny,

I added a key for SurgicalCoid (keyfield for Group,Division,Market, Facility)&ProcedureSurgeonName&ProcGroupDesc AS VarianceID and included the set above with VarianceID as the Key, but the expression is giving the $ variance = to the Cost Per Case and not the correct variance as shown below.

Re: Set Analysis Expression <= $0

sunny_talwar — Tue, 28 Mar 2017 19:27:29 GMT

Did you subtract the variable from your expression?

Re: Set Analysis Expression <= $0

NickHoff — Tue, 28 Mar 2017 19:52:49 GMT

That was it, working as intended. Thanks Sunny!