Current Selections in a Calculation

[#1]

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?

[#2]

Hi Kristen

Does this show your expected count?

sum(if(date1=date2,1))

Regards

Juerg

[#3]

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.

[#4]

Hi Kirsten

You are thinking of something like

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

Regards

Juerg

[#5]

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.

[#6]

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

[#7]

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

[#8]

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.