Set Analysis based on Multiple Selections in a List Box

mahesh_agrawal — Mon, 19 Oct 2015 13:38:38 GMT

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

Re: Set Analysis based on Multiple Selections in a List Box

marcus_sommer — Mon, 19 Oct 2015 14:42:31 GMT

Wouldn't something like this work?

COUNT({<Table_Name={'Customer Status'},

Date={">=$(=MakeDate(2015,08,20))<=$(=makedate(2015,10,19))"},

[No. of Calls] = p([No. of Calls]) >} DISTINCT %Prod_Cust_Comp_Id)

- Marcus

topic Re: Set Analysis based on Multiple Selections in a List Box in QlikView

Set Analysis based on Multiple Selections in a List Box

Re: Set Analysis based on Multiple Selections in a List Box