topic Re: Expression not working in QlikView

Expression not working

bhaveshp90 — Sat, 16 Nov 2024 04:30:31 GMT

Hello,

I have a table as shown below

I am calculating Current - Dec = (Current Month POC - Last Year December month POC)

Eg: for January-2018, 28.30-19.90 = 8.4, for February-2018, 39.40-19.90 = 19.5.

But My Values are wrong as highlighted in the above image.

What is wrong with my expression here? Attached is the file for reference

Sum(POC)
-
Sum({<Num_Month = {'$(vPreYearLastMonth)'},Year = {'$(vPreYear)'}>}POC)

thanks

Bhavesh

Re: Expression not working

sunny_talwar — Fri, 08 Feb 2019 15:25:27 GMT

Try this

Sum(POC)
-
Above(TOTAL Sum(POC))

Re: Expression not working

pradosh_thakur — Fri, 08 Feb 2019 15:25:46 GMT

I think below will do

Column(1)-above(column(1))

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 15:34:35 GMT

@sunny_talwar the expression is working now, but I want Last year December POC as base value, and calculating current month POC - December month POC

Re: Expression not working

sunny_talwar — Fri, 08 Feb 2019 15:40:21 GMT

Try this

Sum(POC)
-
Sum(TOTAL {<Num_Month, Year, MonthYear = {"$(=MinString(MonthYear))"}>}POC)

Re: Expression not working

Marcos_rv — Fri, 08 Feb 2019 15:58:56 GMT

ask, if instead of using set analysis, you have it precalculated at that value? you could use the sentence "Previous" to obtain the value of the previous record, also adding an if you want to make sure that it is Project_Number indicated, you should also order it in a desc way. Regards!!!!

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 16:03:24 GMT

@sunny_talwar it is working fine, but when we include November-2017, or October-2017 the base value changed to November-2017, October-2017. I tried to include these variables in the set analysis which is giving me an error.

Let vPreYear = Year(AddYears(Today(),-2));
Let vPreYearLastMonth = '12';

Re: Expression not working

sunny_talwar — Fri, 08 Feb 2019 16:10:42 GMT

So, what would you expect to see when you select Dec 2016 to Dec 2018 (2 years)

Jan 2017 - Dec 2016
Feb 2017 - Dec 2016
...
Dec 2017 - Dec 2016

Jan 2018 - Dec 2017
Feb 2018 - Dec 2017
...
Dec 2018 - Dec 2017

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 16:15:23 GMT

Yes, what you've written is correct.

Dec 2016 should be base value for all months from Jan-2017......Dec-2017

Dec 2017 should be base value for all months from Jan-2018......Dec-2018

Re: Expression not working

sunny_talwar — Fri, 08 Feb 2019 16:21:30 GMT

Can you please add a sample where we have 2 years like the above mentioned. I will have to test it out before I gave an expression which will work for you.

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 16:32:46 GMT

thank you @sunny_talwar, please look into the attached file;

Re: Expression not working

sunny_talwar — Fri, 08 Feb 2019 16:42:56 GMT

Try this

Sum(POC) - Above(TOTAL Sum(POC), Num(Month))

I think your values for Jan, Feb, Mar are not right.... they should be 0, 0, and 0, right because you have 100-100 for all three months of 2018

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 16:51:16 GMT

Awesome @sunny_talwar, thank you

-you are right, it was a typo error

Re: Expression not working

bhaveshp90 — Fri, 08 Feb 2019 19:28:44 GMT

@sunny_talwar the expression is null when selecting Month field, it is showing correct values when no month is selected.

Since, this post is closed Please look into the link below, thanks

https://community.qlik.com/t5/New-to-QlikView/Expression-is-showing-null-when-selecting-the-Month-field/td-p/1541781