Override list box choice for an expression

[#1]

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

[#2]

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

[sunny_talwar]

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,

S

[#4 · Accepted solution]

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

[sunny_talwar]

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,

S