topic How to modify SET expression so that even now is Sept 2016 it will take in date 1 Dec 2016 ? in QlikView

How to modify SET expression so that even now is Sept 2016 it will take in date 1 Dec 2016 ?

paulyeo11 — Thu, 22 Sep 2016 02:31:26 GMT

Hi All

i have below SET expression working fine :-

=Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}distinct REVENUE_C)

When user select OMRON_1 , TEXT 1 Display zero.

The reasons why user click on OMRON_! it display zero because the date is 1 Dec 2016 or 1 Dec 2015 and so on.In order to make it display , i need to select year = 2015. ( To make it work , i have change the date from 2016 to 2015 , which is not correct. )

How to make it display 12621000 when user select OMRON_1 ? by change the SET expression ?

Remark :- When user click on OMRON , it display 12821000.( Because i have replace 2016 to 2015 , which is not correct ).

I am at china now , unable to upload the QVW file

Paul

Re: How to modify SET expression so that even now is Sept 2016 it will take in date 1 Dec 2016 ?

Anonymous — Thu, 22 Sep 2016 03:33:44 GMT

Create MonthYear field in script and

Aggr(Sum({$< sales = {'*'}>} distinct REVENUE_C),max(MonthYear))

Re: How to modify SET expression so that even now is Sept 2016 it will take in date 1 Dec 2016 ?

jagan — Thu, 22 Sep 2016 05:22:33 GMT

Hi Paul,

Try

=Sum({$<year = {$(=Min(year))}, month = {"<=$(=Max({<year={$(=Min(year))}, sales = {'*'}>} month))"}>}distinct REVENUE_C)

Re: How to modify SET expression so that even now is Sept 2016 it will take in date 1 Dec 2016 ?

paulyeo11 — Fri, 23 Sep 2016 03:15:49 GMT

Hi All

I have try 2 of the expression both of you propose, but it does not work.

Enclosed my QVW.

Hope some one can take a look.

Paul