topic Re: set analysis dates and calendar object in QlikView

set analysis dates and calendar object

Thu, 08 May 2014 12:12:25 GMT

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.

Re: set analysis dates and calendar object

rajat2392 — Thu, 08 May 2014 12:26:36 GMT

Hi QlikAsh,

1. Whenever you create a calendar object, you have to give either a field or a variable. Then Min and Max date you also have to give.

You can create it like a normal calendar where user can make the selection by just giving it the Min and Max date, but it will make no sense until it reflects changes on selection made. For that you have to link it either with a field, so the selection will be reflected in that field and to other objects..or else you can link it with a variable which stores the date selected and use that variable to get the desired results based on the date selected by user.

2. Your second question is not clear...and specifically I must say not making sense, coz you want count of records as per date selected by user falling to dimensions. Can please explain it in better way.

Regards

Rajat

Re: set analysis dates and calendar object

Thu, 08 May 2014 12:59:45 GMT

Hi Rajat!

Thank you so much.

1. Yes, I have want the latter to happen, in other words the date that the user selects gets stored into the variable I have called test. So based on what you said, I just created the variable called test which will store the user date selection. I then specified min date as 2000/01/01 and max date as date(now()).

I noticed though that the system is storing the date into my variable test as a number and not a date.

How would I make it store it as the format above?

2. For your second question, please see the sample below and how I'm trying to tie it back to my set analysis.

Hope it makes more sense now? You were right so far in what you presumed it to be that I required.

Member Number	=test	Effective from Date	Effective to Date	Status Description	Beneficiary Relationship Type	Beneficiary Type	=count([Member Number])
							5
468908 (4)	2002/05/01 12:00:00 AM	2002/05/01 00:00	9999/12/31 00:00	Active	SP	D	1
468908 (0)	2002/05/01 12:00:00 AM	-	-	-	MM	P	1
468908 (1)	2002/05/01 12:00:00 AM	-	-	-	SP	D	1
468908 (2)	2002/05/01 12:00:00 AM	-	-	-	CH	D	1
468908 (3)	2002/05/01 12:00:00 AM	-	-	-	CH	D	1

Re: set analysis dates and calendar object

rajat2392 — Thu, 08 May 2014 13:16:17 GMT

Hi QlikAsh

On selection the date will be stored as number only in the variable, what you can do is apply the date function on the variable, as below

Date($(=test))

Re: set analysis dates and calendar object

Fri, 09 May 2014 05:36:20 GMT

Hi Rajat!

Thank you once again.

I have tried various combinations of date functions but to no avail. I would really like to send you my sample app ... I am unable to add it though as I don't have that option (I only have add video and images). I don't have the advanced editor option anywhere.

Please advise.

Thanks.

Re: set analysis dates and calendar object

jonathandienst — Fri, 09 May 2014 08:38:38 GMT

The document upload option is in the advanced editor - click "Use advanced editor" at the top right of the reply screen.

HTH

Jonathan

Re: set analysis dates and calendar object

Fri, 09 May 2014 08:51:05 GMT

Hi all.

Please find attached the QV app that I needed help with in so far as my set analysis is concerned. I made notes on the app itself but please let me know if further clarity is needed.

Thank you so much!

Re: set analysis dates and calendar object

Fri, 09 May 2014 09:50:11 GMT

Thanks! Hoping my issue in the set analysis can be solved even though I'm using a variable to drive the set.

Regards.

Re: Re: set analysis dates and calendar object

jonathandienst — Fri, 09 May 2014 10:56:22 GMT

Here it is...

HTH

Jonathan

Re: set analysis dates and calendar object

rajat2392 — Fri, 09 May 2014 11:16:50 GMT

Hi Ash,

I have seen your application just now.

In the second expression SET ANALYSIS, you have given "$(test)"

Is it a variable..??

If yes then where have you created it coz in Variable overview I can't find it.

And your variable "vEff_From" and "vEff_To" don't have any value or expression, so what are you are comparing here in the second expression.

Regards

Rajat

Re: set analysis dates and calendar object

Fri, 09 May 2014 18:14:50 GMT

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.

Re: Re: set analysis dates and calendar object

Fri, 09 May 2014 18:15:59 GMT

Thank you so much!

It works perfectly!

Much appreciated.

Re: Re: set analysis dates and calendar object

Wed, 14 May 2014 08:05:15 GMT

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.

Re: set analysis dates and calendar object

jonathandienst — Wed, 14 May 2014 09:41:40 GMT

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

Re: set analysis dates and calendar object

Wed, 21 May 2014 11:47:26 GMT

Hi.

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

Thank you.