topic Re: problem with $(=maxdate-30) in set analysis in QlikView

problem with $(=maxdate-30) in set analysis

Tue, 07 Apr 2015 17:11:16 GMT

Hi,

My expression $(maxdate) -30 gives me the date that is 30 days less than the max date. However If I put my expression like

$(=date(maxdate - 30)), it gives me -2015

for some reasons, the dollar sign expansion is making some problem that I dont understand. In my set analysis, I want to set my expression like

sum({<DATE_POS = {">=(date(DATE_POS - 30))"}>}Profit)

However, it does not seem to work. Can someone please explain what is wrong with my dollar sign expansion expression

Arif

Re: problem with $(=maxdate-30) in set analysis

rogerioqv — Tue, 07 Apr 2015 17:20:20 GMT

Hello,

I would try this:sum({<DATE_POS = {">=$(date(DATE_POS - 30))"}>}Profit)

Rogério.

Re: problem with $(=maxdate-30) in set analysis

Anonymous — Tue, 07 Apr 2015 17:27:34 GMT

This approach always works for me:

sum({<DATE_POS = {"$(='>=' & date(MaxDate-30))"}>} Profit)

Re: problem with $(=maxdate-30) in set analysis

Tue, 07 Apr 2015 17:50:08 GMT

hi,

Try like this....

sum({<DATE_POS = {"$(>= & date(MaxDate-30))"}>} Profit)

Re: problem with $(=maxdate-30) in set analysis

Tue, 07 Apr 2015 18:11:35 GMT

I have changed the expression a little but neither of them work

sum({<CurYTDFlag = {1}, Month={"$( = & month(max(DATE_POS)))"}>}Profit)

and

sum({<CurYTDFlag = {1}, Month={"$( = month(max(DATE_POS)))"}>}Profit)

Re: problem with $(=maxdate-30) in set analysis

Anonymous — Tue, 07 Apr 2015 18:29:53 GMT

(Apparently you never tried my suggestion above.)

After you've changed your logic - your second version is technically correct, and should work. The reasons that it not working may be:

- CurYTDFlag does not have value 1

- values in the Month field are not the same as the function month(max(DATE_POS)) returns.

Re: problem with $(=maxdate-30) in set analysis

Tue, 07 Apr 2015 18:41:33 GMT

Thanks for your answer. Your suggestions seems to work with dates but I cannot get it working with the month expression. I tried it like this

=sum({<Month = {"$(='=' & month(MaxDate-30))"}>} Profit)

However, it does not seem to work. I have also removed the CurYTDFlag. Also, both the Month field and the expression seems to be returning mar. The only problem again is with the dollar sign expansion.

If I try it in a text box

=month(max(DATE_POS))

it returns mar as a value. However, If try

=$(=month(max(DATE_POS)))

It returns nothing

Re: problem with $(=maxdate-30) in set analysis

Anonymous — Tue, 07 Apr 2015 18:49:31 GMT

Typically I test expressions in text boxes as well, but not the $-sign expressions like this

=$(=month(max(DATE_POS)))

But I did test this, and it worked:

=sum({<Date=, MonthName = {"$(=month(max(Date)))"}>} Sales)

And it works fine. Field names differs from yours, I have MonthName with values 'Jan' to 'Dec'. And, the "Date=" was necessary because I selected Date and didn't want it to affect the result, just wanted to define the Month by that selection.