Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

SET expression current month ok previous not ok

Hi All

I have below expression working fine for retuen current selected month amount :-

(

sum({$

<

year = {$(=max(year))},

Month = {"<=$(=max({<year={$(=max(year))}>} Month))>$(=max({<year={$(=max(year))}>} Month)-1)"}

>

}   Amount/1000)

)

I try to modify the above expression to below , in order to display previous month sales :-

(

sum({$

<

year = {$(=max(year))},

Month = {"<=$(=max({<year={$(=max(year))}>} Month))>$(=max({<year={$(=max(year))}>} Month)-2)"}

>

}   Amount/1000)

)

But it return null value. Can some one advise me where go wrong ?

Paul

1 Solution

Accepted Solutions
alexdataiq
Partner - Creator III
Partner - Creator III

You could simplify those expressions by just using:

Current Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month))}>} Amount/1000)

Past Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month)-1)}>} Amount/1000)

But I would suggest create a variable for your previous Month to take into account January, something like:

vPreviousMonth

= If(Max(month) - 1 = 0, 12, month)

And use it in your expression:

Sum({$< year = {$(=max(year))}, month = {$(vPreviousMonth)}>} Amount/1000)

Cheers

View solution in original post

3 Replies
paulyeo11
Master
Master
Author

my QV Doc

alexdataiq
Partner - Creator III
Partner - Creator III

You could simplify those expressions by just using:

Current Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month))}>} Amount/1000)

Past Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month)-1)}>} Amount/1000)

But I would suggest create a variable for your previous Month to take into account January, something like:

vPreviousMonth

= If(Max(month) - 1 = 0, 12, month)

And use it in your expression:

Sum({$< year = {$(=max(year))}, month = {$(vPreviousMonth)}>} Amount/1000)

Cheers

paulyeo11
Master
Master
Author

Hi Alejandro

Thank you very much for your advise. I notice that after you simplify the expression , now user must select current month and year. In fact i have make a lot of change to avoid select select month and year. So the same expression i can use in QS.

Paul