topic Re: Override list box choice for an expression in QlikView

Override list box choice for an expression

Fri, 20 Feb 2015 13:58:31 GMT

Hello,

I have an expression where I'm summing data from 3 different fields. Two of them correspond to a list box selection for the plant code and one should return values from another plant number.

I want the field "Purchase_order_qty" to return values for plant 2000 only regardless of which plant is chosen in the list box. In other words I want to input "Plant=2000" into the formula below, but keep failing to get it right. I have verified that the data exists and comes through to a check-up straight table I created. The bolded underlined part is the way I tried it. No error, but no success either.

sum({<YYYYMM={">$(=Date(AddMonths(Today(),0),'YYYYMM',Plant={2000}>Total <Plant>))"}>}Purchase_order_qty)

Thanks in advance!

-Teemu

Re: Override list box choice for an expression

Sun, 22 Feb 2015 16:10:11 GMT

Try this:

sum({<YYYYMM={">$(=Date(AddMonths(Today(),0),'YYYYMM'))"},Plant={2000}>} Total <Plant> Purchase_order_qty)

Depending on what you need to achieve I don't know if the "Total <Plant>" bit is relevant.

Hope this helps.

Kind regards,

Nuno

Re: Override list box choice for an expression

sunny_talwar — Sun, 22 Feb 2015 20:42:37 GMT

Is your expression without the Plant portion working for you? Meaning is it giving you any output? The reason I asked this because, when I usually use single quotes between the set modifier, it give me an error.

sum({<YYYYMM={">$(=Date(AddMonths(Today(),0),'YYYYMM'))"}>}Purchase_order_qty)??

This is what I would do. Create a variable vYearMonth = Date(Today(), 'YYYYMM') and then use this variable in my expression like this

Sum({<YYYYMM = {'>=$(vYearDate)'}>} Purchase_order_qty

To come to selecting data for only Plant 2000, you can further expand your expression, this way

Sum({<YYYYMM = {'>=$(vYearDate)'}, Plant = {2000}>} Purchase_order_qty

Try and see if the above expression gives you any result or not? (Still not sure why you have Total <Plant> in there, but we can figure that out if we can get some result from the above expression.

HTH

Best,

Re: Override list box choice for an expression

Wed, 25 Feb 2015 10:50:30 GMT

Thanks, guys!

I copied the expression from another one using a different source field so it works ok. I don't want to create the variable, because it would mean a ton of changes to my report.

I was able to bring the right set of data through with sum({<YYYYMM={">$(=Date(AddMonths(Today(),0),'YYYYMM'))"},Plant={2000}>}Purchase_order_qty)

I had to remove the Total <Plant> because it was summing the data for each row. It was something that I had needed for another expression.

BR,

Teemu

Re: Override list box choice for an expression

sunny_talwar — Wed, 25 Feb 2015 11:11:57 GMT

If you got what you wanted, I would suggest closing this thread by marking your answer correct so that it can be helpful for someone in the future.

Best,