Set Analysis with Dates

[#1]

Hi Folks,

after reading so much, i am still not able to build the correct formula for my diagram.

I would lie to calculate the sum of OUTBOUND_DAILY sent yesterday. My formula looks like:

=sum(    {$<Date_Daily={       $(Today()-1)        }>    } [OUTBOUND DAILY])

The Date_Daily is saved in the format: 'DD.MM.YYYY'.

When I use the formula with a fixed date like this, it works perfect:

=sum(    {$<Date_Daily={       '19.04.2012'        }>    } [OUTBOUND DAILY])

Can you help me to enter the formula correct to see each day the previous date.

Thanks so much,

Regards Mathias

[CELAMBARASAN]

Hi,

     Then you have to use the same format here also

     =sum( {$<Date_Daily={'$(=Date(Today()-1,'DD.MM.YYYY'))}>} [OUTBOUND DAILY])

Celambarasan

[chriscammers]

With this problem a couple of things come to mind.

1. Default date formatting, you will want to change the default date formatting for the document. This is done in the load script. This is important because when you call functions like "today" you want the date to look like ALL your other dates in your document MM.DD.YYYY. If you don't do this you will have to qualify the date formatting on every function where you deal with your dates, like this Date(ADateField, 'MM.DD.YYYY')
2. Arithmetic performed on a data field returns a NUMBER not a date. So Date1-Date2 is a number, Max(Date1) is a number, Today()-1 is a number. When you perform arithmetic and you want the result to be a date you have to wrap it in a Date() function. This is part of why I suggest changing the default date format for your document, you'll use the date function alot.
3. Your expression without changin your document should look like this
   • =sum(    {$<Date_Daily={       "$(=Date(Today()-1,'MM.DD.YYYY')"        }>    } [OUTBOUND DAILY])
   • I always have the best luck with set analysis when I use the double quotes as the argument but I've seen lots of combintations work too.
     

Best of luck

Chris

[#4]

Thanks Gentlemen for you quick replies. Also for the detailled description you gave.

I tried both formulas and both don't work. I used the formulas from chrisammers and corrected it slighty (chriscammers wrote 'MM.DD.YYYY' instead of 'DD.MM.YYYY). Furthermore I guess there is a ' or " to much or less in the formular from Celambarasan.

Sorry to ask you again, but both arent' correct. On which part can I work again to get it done?

Thanks,

Mathias

[CELAMBARASAN]

Hi,

     Check with this

     =sum( {$<Date_Daily={$(=Date(Today()-1,'DD.MM.YYYY'))}>} [OUTBOUND DAILY])

[#6 · Accepted solution]

HI Folks,

here we go:

=sum({$<Date_Daily={'$(=(Date((Today()-1), 'DD.MM.YYYY')))' }>}[OUTBOUND DAILY])

The thing was I guess, the -> ' <- needs to be set close around the Date function. Otherwise empty fields are added.

Thanks so much Guys,

you helped a lot,

Cheers,

Mathias