qty sum on a date < today()

[#1]

Hi,

Someone please help me.

I want to sum qty for pur_date < today().

I have put date before pur_date to make the date format similar, and cross checked the also.

I am using following set analysis.

Sum(${date[pur_date]={"<=$(=Today())"}>}qty)

This is not working.

To be noted that the today function is not highlighted in brown/blue color.

What is the issue?

Please help me ASAP.

Thank You,

[rubenmarin]

Hi Rahul, there is a typo:

Sum({$<date[pur_date]={"<=$(=Today())"}>}qty)

Also, the set analisys has to be applied to fields, is 'date[pur_date]' a field? If not, let only the field name where filters has to be applied:

Sum({$<[pur_date]={"<=$(=Today())"}>}qty)

And the format between dates has to be the same:

Sum({$<[pur_date]={"<=$(=Date(Today(), 'Date_Format_of_[pur_date]')"}>}qty)

Hope this helps.

[vinieme12]

Or this to take care of the format

Sum(${<NUM([pur_date])={'<=NUM(Today())'}>}qty)

[#4]

Ruben,

Thanks a ton for the solution.

I can't express my gratitude to you in helping me out. Thanks is very small.

But, one more issue. when I am trying to put a condition Flag='P', this condition is not working.

Please help me once more where and how to add this condition.

Thank you,

[#5]

Sorry, I am afraid, this doesn't work properly.

thanks

[Anil_Babu_Samineni]

Rahul,

Whole application, you want to show till today

Sum({<date[pur_date]={"<=$(Today())"}>} qty)

How you date Format in scripting

Date(Date#(date[pur_date],'MM-DD-YYYY'),'DD-MM-YYYY') as date[pur_date

Try now

[rubenmarin]

Hi Rahul, adding it like another field in set analysis? It can be:

Sum({$<[pur_date]={"<=$(=Date(Today(), 'Date_Format_of_[pur_date]')"}, Flag={'P'}>}qty)

[Colin-Albert]

Vineeth,

Set analysis only works for fields on the left of the = sign, you cannot use an expression at that position.

So Sum(${<NUM([pur_date])={'<=NUM(Today())'}>}qty) will not work. 

You would need to define a new field pur_date_num in the load script as num(pur_date) then an expression like

Sum(${<pur_date_num={'<=NUM(Today())'}>}qty) would work

[vinieme12]

Correct Colin ,  I was pointing out that comparing numbers is always better than comparing than comparing dates.