AGGR function exclude current selection in set analysis

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

Hi All,

I need to show the below metric for the current year and previous year,

I have a listbox of CY_Flag%, in that value is selected as 1 ( which is for current year), The below expression is working fine current year.

but I need to show the value for previous year also, if I select CY_Flag% listbox value as 0, I will get the value for previous year but that time I won't get the value for current year.

If I clear CY_Flag% list box, current year and previous year expressions are working fine, but I can't clear the CY_Flag% listbox.

How can we achieve this in using the expression?

how can we override the CY_Flag% selection?

Current Year expression:

= Num(Count({$<CY_Flag%={1},_TargetCustomer={1}>}

DISTINCT

Aggr(

(

Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={1}>} DISTINCT CPA_Contact_Cnt) >= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={1}>} Contact_Cycle_Plan_Qty) * 0.8

and Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={1}>} DISTINCT CPA_Contact_Cnt)<= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={1}>} Contact_Cycle_Plan_Qty) * 1.2

and sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={1}>} Contact_Cycle_Plan_Qty) > 0

, CPA_Customer_Cnt, null()

)

,CPA_Customer_Cnt,[Cycle Name],Segmentation)

), '#,##00')

Previous Year expression:

= Num(Count({$<CY_Flag%={0},_TargetCustomer={1}>}

DISTINCT

Aggr(

(

Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={0}>} DISTINCT CPA_Contact_Cnt) >= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) * 0.8

and Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={0}>} DISTINCT CPA_Contact_Cnt)<= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) * 1.2

and sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) > 0

, CPA_Customer_Cnt, null()

)

,CPA_Customer_Cnt,[Cycle Name],Segmentation)

), '#,##00')

Thanks,

Sibin Jacob. C

Re: AGGR function exclude current selection in set analysis

sunny_talwar — Fri, 23 Sep 2016 11:50:14 GMT

Try this:

= Num(Count({$<CY_Flag%={0},_TargetCustomer={1}>}

DISTINCT

Aggr(

(

Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={0}>} DISTINCT CPA_Contact_Cnt) >= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) * 0.8

and Count({$<_TargetCustomer={1},_DataSource_F_Call={1},CY_Flag%={0}>} DISTINCT CPA_Contact_Cnt)<= sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) * 1.2

and sum({$<_TargetCustomer={1},_DataSource_F_Target={1},CY_Flag%={0}>} Contact_Cycle_Plan_Qty) > 0

, Only({<CY_Flag%={0}>}CPA_Customer_Cnt), Null()

)

,CPA_Customer_Cnt,[Cycle Name],Segmentation)

), '#,##00')

Re: AGGR function exclude current selection in set analysis

sibin_jacob — Tue, 27 Sep 2016 05:51:52 GMT

Thank you very much Sunny, it is solved the issue...

topic Re: AGGR function exclude current selection in set analysis in QlikView

AGGR function exclude current selection in set analysis

Re: AGGR function exclude current selection in set analysis

Re: AGGR function exclude current selection in set analysis