set analysis dates and calendar object

[#1]

Hi everyone.

I have two questions that I would really appreciate assistance with please:

1. When trying to setup a calendar object, it only seems to want to use either a current dimension in the model or a variable.

How would I make it like any regular calendar picker to make the user select any date they would like and not really a date based on an object in the model?

2. Based on the above, lets say I create a variable called test into which the date that the user selected gets stored. In my set analysis, I would like test to only count records where the date the user picks is between two date dimensions in the model. I used two variables to store each of the dimension values called vEff_From and vEff_To.

Here is my set analysis:

count({$<$(test) = {">=$(vEff_From)<=$(vEff_To)"},[Beneficiary Type] = {"D"}, [Beneficiary Relationship Type] = {"SP"}>} [Member Number] )

An example of the date format of vEff_From is "2002/05/01 12:00:00 AM". Now when the user selects a date from the calendar object which is going to be stored in variable test, I doubt the format might have time in it like this one above? Reason I'm questioning this is because my set analysis might not calculate correctly. PS: I don't need the time component in vEff_From and vEff_To so if you validate my set analysis but first need me to change any of date formats, please let me know about the change I should make as well.

Thank you very much.

Really would appreciate assistance.

[#2]

Hi Rajat.

Yes, when I attached the file, I changed to more meaningful variable names so they are slightly different from the set analysis I sent originally ito the names. I have however managed to find the solution in the response from Jonathan (in case you also want to look at it).

Thank you so much for assisting me.

Regards.

[#3]

Thank you so much!

It works perfectly!

Much appreciated.

[#4]

Hi Jonathan.

Now that my data set has been fully loaded and when I did a more detailed test, I'm finding that my counts are at least three times more than what they should be. So I assume it might be that I need a distinct count. When I

tried to say count distinct at the start of the set analysis, it does not take the syntax. When I tried it inside the set on [Member Number], then my counts get even larger..

count({$<

  [Ben Event Effective from Date] = {"<=$(=Date(vUserInput))"}, 

  [Ben Event Effective to Date] = {">=$($=Date(vUserInput))"},

  [Beneficiary Type] = {"P"},

  [Beneficiary Relationship Type] =,

  //[Beneficiary Relationship Type] = {"SP"},

  [Status Description] = {"Active"}

  >} distinct [Member Number] )

Secondly I also noticed that only when I change the  [Status Description] to "Resigned" that the date filtering does not seem to restrict to dates within the specified vUserInput range but seems to bring back effective dates out of the range as well.

Was wondering if you perhaps had any ideas as to what could be wrong?

Would really appreciate your assistance.

Thank you.

[jonathandienst]

Hi

You might want to ask these questions in a new thread.

Its hard to diagnose these problems without looking at the data. You could upload your QVD:

• To reduce the size, make some selections and then click File | Reduce Data | Keep Possible Values
• If the data is sensitive, you can scramble the some fields so that the sensitive data is encrypted.

HTH

Jonathan

[#6]

Hi.

Thanks. After alot of web issues I've logged a new post "Dates in my set."

Thank you.