12 Replies Latest reply: Jun 23, 2016 7:36 AM by Yeo Poh sai

Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi All

My Table have below expression display YTD sales work fine  :-

Sum({\$<year = {\$(=Max(year)-1)}, month = {"<=\$(=Max({<year={\$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

And below display YTD LY sales :-

Sum({\$<year = {\$(=Max(year)-1)}, month = {"<=\$(=Max({<year={\$(=Max(year))}, sales = {'*'}>} month))"}>}sales/Rate)

My question is when user need to compare whole year 2015 vs 2016 from Jan till june sales , user click on year=2016 and month = 12

the table display null value.

May i know how to make it display YTD sales amount for both column ?

Paul

my qvw

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Not clear from your explanation. Can you provide output which you are looking for?

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi Manish

I have already got the solution on this post , and i have mark correct answer. , that is :-

Try to remove

,sales={'*'}

or replace with

,sales=

Today i learn 2 thing about

,sales={'*'}

1) This allow user not select any value and it will select june.

2) if {'*'} are remove user can select mth =12 and yr=2016 and it can display value.

Paul

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi,

In PY YTD dont set month field.Nullify it.

Try like,

Sum({\$<year = {\$(=Max(year)-1)}, month = , sales = {'*'}>} month))"}>}sales/Rate)

Regards

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi,

or you can simply set Month value in set analysis for PY YTD.

try like,

Sum({\$<year = {\$(=Max(year)-1)}, month = {"<=12"}, sales = {'*'}>} month))"}>}sales/Rate)

Regards

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi Max

i try it return null value.

Paul

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi,

It must work . Try below

Sum({\$<year = {\$(=Max(year)-1)}, month = {"<=12"}>}sales/Rate)

or

Sum({\$<year = {\$(=Max(year)-1)}, month = >}sales/Rate)

Regards

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi Max

Sure I will try, thank you for so confident in yr expression. As now I take a break for my dinner.

Sent from my iPhone

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi Max

I just test , it work fine.

Thank you

Paul

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Try to remove

,sales={'*'}

or replace with

,sales=

• Re: Does SET expression allow user select yr=2016 and mth =12 and still able to display value ?

Hi Antonio

Thank you very much , this is what i am looking for.

Paul