Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

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

1 Solution

Accepted Solutions
paulyeo11
Valued Contributor II

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

12 Replies
paulyeo11
Valued Contributor II

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

my qvw

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

Regards,
Prashant Sangle

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?

krishnamraju513
Contributor

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

You try to use ADDMONTHS() & ADDYEARS() to your previous year & Month .

antoniotiman
Honored Contributor III

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,

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

Regards,
Prashant Sangle
paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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

Community Browser