topic Re: Set Analysis using Dates in QlikView

Set Analysis using Dates

Anonymous — Thu, 20 Jun 2013 11:18:04 GMT

I'm haing a problem using dates in my set analysis.

Included is a sample to illustrate the problem.

I really just need to be able to sum the values related to a period going back from the current date i.e. last 3 months, or last 12 months etc.

Any help would be greatly appreciated.

Thanks.

Joao.

Re: Set Analysis using Dates

er_mohit — Thu, 20 Jun 2013 11:29:50 GMT

see the attached file

Re: Set Analysis using Dates

Thu, 20 Jun 2013 12:00:00 GMT

Just share some data which u r using

Re: Set Analysis using Dates

Thu, 20 Jun 2013 12:30:20 GMT

Hi check below Last3month sum script

Create date as TRANDATE(DD/MM/YYYY)

SET vLTMonth =Date(Max(TRANDATE),'DD/MM/YYYYY') ;

SET vLastThreeMonth= =monthstart(vLTMonth) ;

Sum( {<TRANDATE={'>=$(vLastThreeMonth)<=$(vLTMonth)'},Month=,Year= >}Value)

Hope it will help u

Regards-Bika

Re: Set Analysis using Dates

Anonymous — Fri, 21 Jun 2013 06:22:37 GMT

Thanks for this. It is very useful.

Please can you confirm that what you are telling me, is that I can't achieve what I want to with the date format that I currently use, which is YYYY-MM-DD i.e. I have to change this to DD-MM-YYYY or DD/MM/YYYY?

Joao

Re: Set Analysis using Dates

Anonymous — Fri, 21 Jun 2013 06:23:41 GMT

Thanks, but please confirm that I can't do this with the date format I am using (QlikView model attached). Both suggestions imply that I must change this.

Joao.

Re: Set Analysis using Dates

er_mohit — Fri, 21 Jun 2013 06:38:10 GMT

try this to change format

date(date#(Datefield,'YYYY-MM-DD'),'DD/MM/YYYY') AS Date,

Date(Datefield,'DD/MM/YYYY') as Date

hope it helps

Re: Set Analysis using Dates

Fri, 21 Jun 2013 12:14:47 GMT

Hi it will help u...

Regards-Bika

Re: Set Analysis using Dates

er_mohit — Mon, 24 Jun 2013 05:36:00 GMT

if your problem resolved then to close thi thread mark it as correct or helpful answer so that it might be helpful for other developer

Regards

Mahit

Re: Set Analysis using Dates

Anonymous — Mon, 24 Jun 2013 05:54:07 GMT

Will do.

Unfortunately, I am not there yet.

I'm not sure how / where to try implementing the above. I would prefer the option of not having to change my date formats (if this is an option). If it isn't, do I change it at time of import, or can I leave everything as is, and implement this change only where I do the set analysis (which will be extensive)?

I will play around with this to check what works, and close the call when I am happy with the solution. I will at the very least mark it as helpful once I have confirmed that it resolves the issue (even if it doesn't exactly address what I am trying to achieve).

Thank you very much for your assistance.

Regards,

Joao.