Hi Guys,
Need help on implementing following logic.
Scenario:
Suppose there are two Table as per attached excel file.
Call Table contains the records for Calls made to each combination of customer and products. No. of Call for a particular combination in
a period can be calculated by taking count of Calls_Flag column i.e. count(Calls_Flag).
Customer Status table is a Slowly Changing Dimension which contains status of each Customer and product combinations.
Requirement:-
User should have a filter name "No. of Calls" containing values 0,1,2,2+.
Another filter "Comparison Time" containing values "30 Days Ago","60 Days Ago" and "90 Days Ago".
The report will always be based on Current Date i.e. Today().
When user selects "Comparison Time" as "60 Days Ago" and "No. of Calls" as 1 and 2, a text box should show the count of Customer and Product combinations
(i.e. count(%Prod_Cust_Comp_Id )) in "Customer Status table" which are having No. of Calls made as 1 or 2 in the period between "Current date" and "60 Days Ago"(i.e. between 19th Oct 2015
and 20th Aug 2015).
Note: I have already implemented the logic when only one selection is made in "No. of Calls" filter. Below is the logic.
COUNT(DISTINCT
{<Table_Name={'Customer Status'},
%Prod_Cust_Comp_Id =
{"=
count(DISTINCT {<Table_Name={'Call Table'},
Date={'>=$(= MakeDate(2015,08,20))'}*{'<=$(=makedate(2015,10,19))'}
>}[%Call Detail ID]) = max([No. of Calls])"}
>}
%Prod_Cust_Comp_Id
)
I need the logic when user selects multiple values in No. of Calls filter. And, I can't take min() or max() of selected values.
Thanks in advance.
Cheers,
Mahesh
