topic Current Selections in a Calculation in QlikView

Current Selections in a Calculation

Wed, 02 Jun 2010 15:58:04 GMT

I am trying to calculate a count of all records when date1 is selected where date2 is within the date1 selected.

So, if current selection of date1_MY = date2_MY then count

Anyone know of a function I can call?

Current Selections in a Calculation

Wed, 02 Jun 2010 16:06:22 GMT

Hi Kristen

Does this show your expected count?

sum(if(date1=date2,1))

Regards

Juerg

Current Selections in a Calculation

Wed, 02 Jun 2010 16:13:50 GMT

Hey Juerg,

Not exactly. Say for date1 Jan-2010 and Feb-2010 were selected. The row can have date1=Jan-2010 and date2=Feb-2010. I still want to add that in my count.

I think I might need to use getcurrentselections() or something like that.

Current Selections in a Calculation

Wed, 02 Jun 2010 16:48:46 GMT

Hi Kirsten

You are thinking of something like

sum(if(substringcount(getfieldselections(date1),date2)>0,1))

Regards

Juerg

Current Selections in a Calculation

Wed, 02 Jun 2010 16:57:05 GMT

Hey Juerg,

I was playing with something like:

Count ({< Date2_MY = {"$(=GetFieldSelections(Date1_MY))"} >} DISTINCT key)

I can get it working if one date1_MY is selected but not mulitiple ones.

Current Selections in a Calculation

Wed, 02 Jun 2010 17:28:52 GMT

Kristen

For multiple selections GefFieldSelections will return more than one month which will then not match your Date2_MY. You should try to get rid of the month's not matching Date2_MY. Do not see a way myself to make this work, sorry

Juerg

Current Selections in a Calculation

Thu, 03 Jun 2010 07:07:30 GMT

Hi Kirsten

played around a little bit but could not make it work, tried

count({<date2={"$(=if(findoneof(getfieldselections(date1),date2)>0,date2)"}>} d1)

but as

if(findoneof(getfieldselections(date1),date2)>0,date2)

works it does not work in the set analysis.

So my only solution is

count( DISTINCT if(substringcount(getfieldselections(date1),date2)>0,key))

Regards

Jürg

Current Selections in a Calculation

Thu, 03 Jun 2010 14:38:17 GMT

Thanks Jurg for looking into it!

Here is how I finally got it to work:

I first created a variable and named it vActiveDateMY. Then I set the value to:

'"'&GetFieldSelections(Date1_MY,'","')&'"'

This set up the variable to go into the set analysis in a way it can understand it. Like if a Jan, Feb, March of 2010 were selected it would show

"Jan-2010","Feb-2010","Mar-2010"

Then I made my expression:

Count ({< Date2_MY = {$(=vActiveDateMY)} >} DISTINCT Key))

Another thing I had to add was if no months are selected to count all distinct keys but that is the idea anyway if anyone else is stuck on the same problem.