filtering through set analysis.

[#1]

Hi all,

I have a 4 KPI's in my sheet and two filter pane.

each one refers to the different counts and filter pane is for client and batch.

when i filter through the client filter pane the count shows only for that client.

i have a different start date for the different clients/batch. when i select the particular client the data should filter.

like >= startdate of that client/batch.

and without any filter it should show the all the values.

How can i use the set analysis?

Currently i am using below expression :  Count({$<DateType = {invitation}>}invitation_id)

I tried with the new expression :  Count({$<DateType = {invitation}, canonicaldate = {'>=date(start_date)'}>}invitation_id)

It's working for client specific, but without client specific i need all the counts.

Regards,

Pramod

[#2]

HI,

please find the sample .qvf.

In the sheets the counts are showing without any filter.

once i applied filter through client_id the filter should happen according client and each client has some start_date . so the counts should also filter on date basis. (>= start_date)

once filter is cleared the it show all the values.

[Gysbert_Wassenaar]

If you use the exact case sensitive field names then the expression works fine:

count({<DateType={invitation}>} if(CanonicalDate > start_date, invitation_id))

[#4]

It's not working.

You can check in the sheet that the CTR count without canonical date it's showing 102498 but with canonical date is showing 29652 when no selection is made.

The canonical date must apply only when the client_id is selected not without any selection.

I think this is clear now.

[Gysbert_Wassenaar]

It's not clear to me.

The canonical date must apply only when the client_id is selected not without any selection.

What does this mean? What is the correct count without a selection of a client_id and what is the correct count if a client_id is selected? Please give examples.

[#6]

start_date is client specific.

For example: client_id = 15 has start_date 11-30-2014.

when i select the client_id 15, i need the CTR count from date 11-30-2014 with respect to client_id 15.

If i have not selected any client_id through filter pane then i need the CTR count of all clients in db.

For client_id 15 CTR count is 4941. without selecting any client_id the count is 102498.

Here i am taking CTR for example but i need to implement for all the KPI's and charts.

[Gysbert_Wassenaar]

I still don't understand what you want. Your expression count(invitation_id) returns 4941 when you select client_id 15. Is that correct? Do you want to select client_id 15 and see 4941 as CTR value? If so then use count(invitation_id). If not please explain what you want to select or not select and where you expect which value to be shown.

[#8]

From count(invitation_id) now i am getting CTR count 4941, that count is complete count of client 15.

But i need the count from start_date (11-30-2014). when i select client 15.

If i select the count from 11-30-2014 the count will vary. The count will decrease.

Same way for client_id 12, now CTR is 1894. i need count from start date 04-29-2014. not before the start date.

When any client is not selected then the CTR count should be 102498 it should not change.

I think now it is clear.

[Gysbert_Wassenaar]

I still don't understand. To begin with what start date needs to be used for client_id 12? There are several.

Next why do you think that count({<DateType={'invitation'}>} if(CanonicalDate>=start_date,invitation_id)) returns incorrect results? Select a client_id and count the records in the table on the second sheet in the app I posted where DateType is invitation and the CanonicalDate is larger then the start_date. I get the same number as the count expression returns.

[#10]

For client id 12 the start date is 04-29-2014 you can see in the batchmetadata table.

I have data before that date for that client.

You can see two CTR in the sheet. Below counts is when client_id 12 is selected.

1. CTR1 count :1892 when using count({<DateType={'invitation'}>} if(CanonicalDate>=start_date,invitation_id))

2. CTR2 count : 1894 when using count(invitation_id)

Above CTR1 count is correct. i need count after the start_date 04-29-2014.

Without selecting the any client_id.

1. CTR1 count : 29652

2. CTR2 count : 102498

Here CTR2 count : 102498 is correct. I need all the count from the DB when no client is selected.

Each client_id has different start_date when i select the client in filter pane the data should filter according to the  start_date of client selected.

I think you got it.