Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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')
Thank you very much Sunny, it is solved the issue...