topic Re: Modify SET expression from YTD and Month Close. in QlikView

Modify SET expression from YTD and Month Close.

paulyeo11 — Tue, 29 Mar 2016 01:33:59 GMT

Hi All

I have below expression , it will return YTD sales :-

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

Now i need to modify the above expression to display month closed. Meaning only display Jan and Feb sales amount exclude march sales amount.

So the result should only shown 1461

Paul

Re: Modify SET expression from YTD and Month Close.

paulyeo11 — Tue, 29 Mar 2016 01:34:45 GMT

My Qv Doc!

Re: Modify SET expression from YTD and Month Close.

jonas_rezende — Tue, 29 Mar 2016 12:21:13 GMT

Hi, paul yeo.

Excluding the march month, not would be 2461? Please, confirm.

Regards,

Jonas Melo.

Re: Modify SET expression from YTD and Month Close.

sunny_talwar — Tue, 29 Mar 2016 12:23:21 GMT

I think this one got already resolved, isn't it Paul?

Sum({$<year = {$(=Max(year)-0)}, month = {"<$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/$(Columndim89)/1000)

Re: Modify SET expression from YTD and Month Close.

paulyeo11 — Tue, 29 Mar 2016 12:42:38 GMT

Hi Jonas

Many SET expert here already give me the correct solution , as for the amount is not important , as my QV sampledata file keep changing. most impt is the right expression which already confirm by the expert here. Now i aware that the = sign is to handle month closed. and '*' is to handle don't select any date.... very interesting discovery.

Paul