topic How to Exclude particular Row Values in Set Analysis Aggregations in QlikView

How to Exclude particular Row Values in Set Analysis Aggregations

Anonymous — Tue, 10 Feb 2015 02:44:39 GMT

Friends,completely blank on set analysis conditions can any one look into it and help on this please.

Below is need to shown in st table

Attaching ref sample data and QVW please look into it help on this.

To Get GP Measure:condition is Sum(Actual Pre),Base Indicator={1},YTD Indicator={1} if i write expression as

sum({$<[Base Indicator]={1},[YTD Indicator]={1}>}[Actual Pre]

But it is sum up all values in above ref exp:GP=30(Wrong)

GP=28(Expected)

Re: How to Exclude particular Row Values in Set Analysis Aggregations

anbu1984 — Tue, 10 Feb 2015 02:56:40 GMT

I dont see [Actual Pre] field in your input Sample file on Renewal tab

Re: How to Exclude particular Row Values in Set Analysis Aggregations

anbu1984 — Tue, 10 Feb 2015 03:06:53 GMT

If you want to exclude API='NO' from your total, try this expr

If(Dimensionality() = 0, Count({$<[Base Indicator]={1},[YTD Indicator]={1},API-={'NO'}>}[Actual Pre]),Count({$<[Base Indicator]={1},[YTD Indicator]={1}>}[Actual Pre]))

Re: How to Exclude particular Row Values in Set Analysis Aggregations

Anonymous — Tue, 10 Feb 2015 03:09:21 GMT

Thanx Anbu pointing my Data defect ,Now corrected [Actual Pre] missed while copying data field in Renewal Tab [B2-B30]

Re: How to Exclude particular Row Values in Set Analysis Aggregations

anbu1984 — Tue, 10 Feb 2015 03:18:16 GMT

You can try the expression in my below post.

But how did you get GP=14 for A. Are you doing count? Still it will not match

Re: How to Exclude particular Row Values in Set Analysis Aggregations

Anonymous — Tue, 10 Feb 2015 04:01:08 GMT

To tell my problem posted excel table in random example,nothing more to consider on it need sum GP in data

Re: How to Exclude particular Row Values in Set Analysis Aggregations

anbu1984 — Tue, 10 Feb 2015 04:13:15 GMT

Ok then replace Count by Sum in above expr