6 Replies Latest reply: Oct 21, 2015 6:10 AM by Colin Albert

# My SET analysis expression return full year amount

Hi All

I have below expression , i want it display LY YTD amount , it does not work , it display whole of last year amount , May i know where i go wrong ?

sum({\$<year = {\$(=max(year)-1)}

,[Reporting Code] = {

">=00100<=

00374"

}

, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} Amount*-1/1)/1000

• ###### Re: My SET analysis expression return full year amount

What kind of values does your month field contain? If they are text values then <= won't work. You need numeric values for a smaller or equal to comparison to work.

• ###### Re: My SET analysis expression return full year amount

Try    month = {"<=\$(=max(month))"}>}

• ###### Re: My SET analysis expression return full year amount

Hi All

I think i know the problem , because i am using the expression at Qlik Sense , and i never select month = 10 ( current month ) that why it display full year amount .

Any way you guy can help me modify the expression , so that even i never select month = 10 it will auto select current month.

PAul

• ###### Re: My SET analysis expression return full year amount

This will select the current month

month = {"<= \$(=month(today())"}

or

month = {"<= \$(=month(today()-1)"}       to use yesterday's date

• ###### Re: My SET analysis expression return full year amount

Hi Colin

Does it mean below expression will give me last month value ?

month = {"<= \$(=month(today()-1)"}

what i need it last year YTD value , meaning 2014 Jan till Oct

Paul

• ###### Re: My SET analysis expression return full year amount

month = {"<= \$(=month(today()-1)"}   just gives you the months for yesterday.

So if you open the report on the 1st Nov, the month will be Jan - Oct not Jan - Nov