topic Re: My SET analysis expression return full year amount in QlikView

My SET analysis expression return full year amount

paulyeo11 — Tue, 20 Oct 2015 15:41:04 GMT

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

Gysbert_Wassenaar — Tue, 20 Oct 2015 15:45:24 GMT

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

Colin-Albert — Tue, 20 Oct 2015 15:46:05 GMT

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

Re: My SET analysis expression return full year amount

paulyeo11 — Tue, 20 Oct 2015 15:51:27 GMT

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

Colin-Albert — Tue, 20 Oct 2015 15:58:42 GMT

This will select the current month

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

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

Re: My SET analysis expression return full year amount

paulyeo11 — Tue, 20 Oct 2015 16:05:24 GMT

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

Colin-Albert — Wed, 21 Oct 2015 10:10:21 GMT

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