Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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

1 Solution

Accepted Solutions

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

6 Replies

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.


talk is cheap, supply exceeds demand

Re: My SET analysis expression return full year amount

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

paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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

Community Browser