Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
sibin_jacob
Contributor 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
Highlighted

Re: AGGR function exclude current selection in set analysis

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
Highlighted

Re: AGGR function exclude current selection in set analysis

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

Highlighted
sibin_jacob
Contributor III

Re: AGGR function exclude current selection in set analysis

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