topic Set Analysis - Dates in QlikView

Set Analysis - Dates

rajeshvaswani77 — Sat, 09 Jul 2011 08:41:23 GMT

Hi,

I have the below expression. This expression returns me zero. Not sure where I am wrong.

=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=addmonths($(varPreYearFullDateEnd),1)"}>} DISTINCT [ClaimID])

$(varPreYearFullDateEnd) has been assigned =Date('31/12/2010','DD/MM/YYYY'). I want to do the calculation for 31/01/2011 by adding 1 month.

On the other hand.

=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(varPreYearFullDateEnd)"}>} DISTINCT [ClaimID]) just works fine.

returns me the correct value. I just want to add 1 month. I tried many ways where I will have date in the number format, use sum etc..finally asking the community to help.

Thanks in advance

Rajesh Vaswani

Set Analysis - Dates

swuehl — Sat, 09 Jul 2011 09:51:29 GMT

Hi Rajesh Vaswani,

you need a dollar sign expansion also for the addmonths function to call it within the set expression:

=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(=addmonths($(varPreYearFullDateEnd),1))"}>} DISTINCT [ClaimID])

Regards,

Stefan

Re: Set Analysis - Dates

IAMDV — Sat, 09 Jul 2011 10:04:44 GMT

Hi Rajesh,

Stefan is right and using dollar sign expansion should do the trick. Although with these kind of requirement I generally prefer to load the master calendar table in the load script. I had attached an example of master calendar for your reference. If you look into the master calendar you will see I have MTD, WTD, YTD & previousYear etc. Also, if I am re-using same expressions within other objects then I would declare my expression as variables and use the dollar sign expansion. This approach helps me to maintin the calculations in one centralised location (Variable Overiew Window). And the re-usability & maintainence of these expression is easy. This is just like adding an abstraction layer.

I hope this helps!

Cheers - DV

Re: Set Analysis - Dates

rajeshvaswani77 — Sun, 10 Jul 2011 12:44:58 GMT

Hi Stefan,

Thankyou for your reply. After $ expansion too it does not work. I am trying to figure out why. Let me know if you could suggest anything?

Thanks,

Rajesh Vaswani

Re: Set Analysis - Dates

nagaiank — Mon, 11 Jul 2011 01:46:56 GMT

The following expression seems to work

=Count({<[link_claim_Claim_Status]={"*"}-{"CLO"},[StartDate]={"<=$(varPreYearFullDateEnd+1)"}>} DISTINCT [ClaimID])

Set Analysis - Dates

Mon, 11 Jul 2011 09:10:50 GMT

Accrding to me Variable should be assigned as:

varPreYearFullDateEnd= assign it : =makedate('2010','12','31')

Re: Set Analysis - Dates

swuehl — Tue, 12 Jul 2011 09:10:29 GMT

Hi Rajesh Vaswani,

I noticed two issues:

with variable definition like

=Date('31/12/2010','DD/MM/YYYY')

I needed to remove the $() around the variable name when using the variable as argument to AddMonths function, i.e.

{<StartDate= {"<=$(=AddMonths(varPreYearFullDateEnd,1))"} >}

(I also struggle from time to time with variable evaluation).

It is also important, that there is no space between the smaller than (<=) and the dollar sign of the dollar sign expansion (I get zero as result otherwise).

As Erika wrote, I think a MakeDate would be clearer to understand what you want to achieve (assigning a date to a variable), so your expression should work as well.

In my test app, it works quite well, if you have any more problems, it may be useful if you could post a small sample application.

Regards,

Stefan