topic Re: Issue in expression- last year MTD in QlikView

Issue in expression- last year MTD

Anonymous — Wed, 10 Oct 2018 06:04:52 GMT

Hi Friends

I am using below expression to get last year MTD.

Example , if today is 10th, then we should get Oct 1st 2017 to 10th 2017. However using below expression is giving me 0.

sum( {<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} SALES)

Kindly help to resolve the issue in above expression.

Thanks & Regards

Chintan Gala

Re: Issue in expression- last year MTD

Anonymous — Wed, 10 Oct 2018 06:13:06 GMT

Give an example of your date format please

Re: Issue in expression- last year MTD

Anil_Babu_Samineni — Wed, 10 Oct 2018 06:15:47 GMT

You can simplify this?

Sum({<DateField = {">=MonthStart(Max(DateField))<=$(=MonthEnd(Max(DateField)))"}>} SALES)

Re: Issue in expression- last year MTD

jonathandienst — Wed, 10 Oct 2018 06:16:14 GMT

Assuming you are making selections in Year and Month (and not in other date fields like Quarter) and that there is data for last year, then the problem might be that Date is not in the default date format for your system.. Then you may get success with

sum({<Year, Month, Date = {">=Date($(=AddYears(MonthStart(max(Date)),-1)), '<date format here>') <=Date($(=AddYears(max(Date),-1)), '<date format here>')"}>} SALES)

Enter the date format for Date into the format strings above to align the date formats.

Re: Issue in expression- last year MTD

MindaugasBacius — Wed, 10 Oct 2018 06:21:51 GMT

Try this:

=sum({<Date = {">=$(=Date(AddYears(MonthStart(Today()), -1), 'YYYY.MM.DD'))<=$(=Date(AddYears(Today(), -1), 'YYYY.MM.DD'))"}>}SALES)

Re: Issue in expression- last year MTD

Anonymous — Wed, 10 Oct 2018 06:32:06 GMT

Dear Anil

I believe this will not give last year MTD. We are trying to achieve for Oct 1 to 10 , 2018.

Thanks.

Re: Issue in expression- last year MTD

Anil_Babu_Samineni — Wed, 10 Oct 2018 08:29:05 GMT

Not sure, Why last year MTD you need? But, Try this to get last Year date from Oct 1st 2017 - Oct 31st 2017 ??

Sum({<DateField = {">=MonthStart(AddYears(Today(),-1))<=$(=MonthEnd(AddYears(Today(),-1)))"}>} SALES)

Re: Issue in expression- last year MTD

Anonymous — Wed, 10 Oct 2018 09:02:46 GMT

Still getting 0 with this. I replaced the date format with DD-MM-YYYY

Re: Issue in expression- last year MTD

MindaugasBacius — Wed, 10 Oct 2018 12:13:34 GMT

Try to debug and split to smaller steps:

Create text objects for:

1. =Date(AddYears(MonthStart(Today()), -1), 'YYYY.MM.DD')

2. =Date(AddYears(Today(), -1), 'YYYY.MM.DD')

Do you get what you are expecting?

Re: Issue in expression- last year MTD

Anonymous — Wed, 10 Oct 2018 12:25:39 GMT

Yes in text objects we get , Oct 1 2017 and Oct 10 , 2017 correctly. But when we are using in expression it gives 0 .

Re: Issue in expression- last year MTD

MindaugasBacius — Wed, 10 Oct 2018 13:44:05 GMT

Then simply check if it works with either one switch between:

1. =Date(AddYears(MonthStart(Today()), -1), 'YYYY.MM.DD')

2. =Date(AddYears(Today(), -1), 'YYYY.MM.DD')

Check if the Date and SALES fields are named the same in the script.

Check if Date formatting is correct 'YYYY.MM.DD'.

Simple play & debug the sentence till you understand what's wrong.