Set Analysis using Dates

[Anonymous]

Hi

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.

[er_mohit]

see the attached file

[#3]

Just share some data which u r using

[#4]

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

[Anonymous]

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

[Anonymous]

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.

[er_mohit]

try this to change format

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

or

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

hope it helps

[#8]

Hi it will help u...

Regards-Bika

[er_mohit]

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

[Anonymous]

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.