Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hello!
SUM({$<WOType={'Productive'}, WOLDepartment={'Maintenance'}, RefDate={"<=$(=date(Today(0)-1))"}>} BdgTotalTime)
Hello!
SUM({$<WOType={'Productive'}, WOLDepartment={'Maintenance'}, RefDate={"<=$(=date(Today(0)-1))"}>} BdgTotalTime)
Awesome, thanks a lot
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
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
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
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