Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibin_jacob
Creator III
Creator III

AGGR function exclude current selection in set analysis

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(

If

(

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(

If

(

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

DISTINCT

Aggr(

If

(

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

View solution in original post

2 Replies
sunny_talwar

Try this:

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

DISTINCT

Aggr(

If

(

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

sibin_jacob
Creator III
Creator III
Author

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