Set analysis with date limitation

[#1]

Hi,

I had a working set analysis expression that limited the date from the start of the fiscal year until today:

sum({$<Abschlusstermin.P= {">=$(=date('$(vFYStart)'),'YYYYMM')<=$(=date('$(vToday)'),'YYYYMM')"}>} [TCV Reportet (Produkt).P])

After a while and several changes to the scripts and the front end, I figured out that the limitation is not working anymore, so it shows all TCV values, not only from FY start to today.

Not sure if this is related to this change: the format for 'Abschlusstermin.P' in the load from was 'YYYYMM', now it's 'MM/YYYY'. I changed the expression to be

sum({$<Abschlusstermin.P= {">=$(=date('$(vFYStart)'),'MM/YYYY')<=$(=date('$(vToday)'),'MM/YYYY')"}>} [TCV Reportet (Produkt).P])

, but this won't work. Any ideas what is going wrong?

Thanks.

[CELAMBARASAN]

Hi,

In Script how you created this format 'MM/YYYY' using date() or combination of Month() and Year()?

Or go and check in the Table viewer, hover the field Abschlusstermin.P and check the tags associated to it whether its $numeric?

[#3]

In the script, i convert from old to new format:

date(date#(SF_TARGET.Monat,'YYYYMM'),'MM/YYYY') as Abschlusstermin.P

The table viewer states Key, Numeric, Integer, Timestamp, Date,

[CELAMBARASAN]

Try this

sum({$<Abschlusstermin.P= {">=$(=date(vFYStart,'MM/YYYY')<=$(=date(vToday, 'MM/YYYY')"}>} [TCV Reportet (Produkt).P])

it will work if vFYStart is created with proper date

[#5]

It is not working, even if I replace both variables with '01.04.2014' and '05.06.2014'.

[CELAMBARASAN]

sum({$<Abschlusstermin.P= {">='$(=date(vFYStart,'MM/YYYY')'<='$(=date(vToday, 'MM/YYYY')'"}>} [TCV Reportet (Produkt).P])

Upload your sample app to look in to this further

[#7]

Ok, I tried to create a simple one. Hope it helps.

[tresesco]

=sum({$<Abschlusstermin= {">=$(=date(vFYStart,'MM/YYYY'))<$(=date(vToday, 'MM/YYYY'))"}>} TCV)

[#9]

This is really close to what I'm looking for, thanks a lot.

I also have target values to calculate the same way, problem is for the year-to-date I would need to add the current month, currently only the last two months are calculated.

And for the expression above with the TCV, I also need to calculate to the current day.

How can I do this?

Thanks again.

[tresesco · Accepted solution]

Just an additional equal sign '=' would be required, like:

=sum({$<Abschlusstermin= {">=$(=date(vFYStart,'MM/YYYY'))<=$(=date(vToday, 'MM/YYYY'))"}>} TCV)