My SET analysis expression return full year amount

[paulyeo11]

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

[Gysbert_Wassenaar]

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.

[Colin-Albert]

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

[paulyeo11]

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

[Colin-Albert]

This will select the current month

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

or

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

[paulyeo11]

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

[Colin-Albert · Accepted solution]

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