Set Analysis Problem

[#1]

Hi

I'm trying to create a sum of Budget values with a set analysis. I guess it's just a small issue, but I'm somehow stuck with it. Why do I get a value of zero when there is clearly a value? I actually would like to look the formula like this:

SUM({$<WOType={'Productive'}, WOLDepartment={'Maintenance'}, RefDate={"<=$(=Today(0)-1)"}>} BdgTotalTime)

but I only get a 0 for that. So I tried to simplify the set analysis and tried to sum a single day only.

=SUM({$<WOType={'Productive'}, RefDate={"42125"}, WOLDepartment={'Maintenance'}>} BdgTotalTime)

Again, I only get a value of 0. If I remove the RefDate completely I do get the value of the BudgetTotalTime.

RefDate is a normal date field in my calendar. Funny enough, the very same set analysis does work in a different app.

I'm happy to get any hints as to where to look.

Thanks in advance.

[pokassov · Accepted solution]

Hello!

SUM({$<WOType={'Productive'}, WOLDepartment={'Maintenance'}, RefDate={"<=$(=date(Today(0)-1))"}>} BdgTotalTime)

[#3]

Awesome, thanks a lot

[Anonymous]

check your date format (RefDate)

when you give (Today(0)-1) it will return numeric format and it will not match with your RefDate,

when you give Date(Today(0)-1) it will return date format with 'DD/MM/YYYY'

So you need to check if your date is in same format 'DD/MM/YYYY' or its in 'MM/DD/YYYY'

If this is the case , you need to change the format of your column or =Date(Today(0)-1,'MM/DD/YYYY')

Thanks

BKC

[r_wroblewski]

Hi Matthias,

it looks like just a small issue with your date format.

RefDate is in which format ?  in Num format or YYYY/MM/DD .....

Today() is by default in the date format defined in your load script (e.g. SET DateFormat='YYYY/MM/DD';)

Because the Set Analysis is working in a different app, it is 99,9% just a date format issue.

Regards,

Ronny

[#6]

The load script has (in both apps) a date format of

SET DateFormat='DD.MM.YYYY';

I also copy pasted the same calendar script from one to the other app, so I assume that RefDate has the same date format.

Yet with the inclusion of the "date" as per Sergei's reply, it works (in both apps): RefDate={"<=$(=date(Today(0)-1))"}

Surprisingly, in my other app it works both ways (with or without the date()), but at least my issue is solved.

Thanks for all the replies

[r_wroblewski]

Hi Matthias

I checked again and the reason why you need Date() is because Today()-1 will change format from your default to numeral format.

e.g. Today() = 2015/05/27 (default setting)

      Today()-1 = 42150

      Date(Today()-1) = 2015/05/26

so the -1 change the format automatically and you need date() to bring it back to the correct format for your set analysis.

I also learnt again something new.^^

Regards,

Ronny