12 Replies Latest reply: Oct 9, 2011 10:54 AM by Jonathan Dienst

# Compare Data of Today to Yesterday

Hey Guys, first post...

small question though, beginners one :-)

I want to SUM the COST for a certain day, using the day i currently have... in other words, if i select the 2nd of july i want to sum the amount of the 1st, if i select the 1st i want to sum the 30/06 and so on...

I was sure it's going to be simple but im totally stuck!

I tried this with no luck: =sum({<Day=Date(Day-1)>} Cost)

(Day is my field of date)

thanks!

Zohar.

• ###### Re: Compare Data of Today to Yesterday

Hi Zohar,

Let try this set analysis SUM({<Day={\$(=Max(Day-1))}>} Cost)

Hope this help.

Regards,

Sokkorn

• ###### Compare Data of Today to Yesterday

Try

=sum ({<day ={"=\$(=date (Max(day)-1))"}>} Cost )

Stefan

• ###### Re: Compare Data of Today to Yesterday

Hey guys,

Apperantly Stefan's and Sokkorn answer was extremly close but didn't work

It showed me the cost of the selected day for some reason, and not the cost of the day before...

this is what i wrote down:

=sum ({<Day ={"=\$(=Date(Max(Day)-1))"}>} Cost )

Any more suggestions?

Thanks!!

Zohar

• ###### Re: Compare Data of Today to Yesterday

Hi Zohar,

Can you attached a small application? So we can find the problem for you.

Regards,

Sokkorn

• ###### Re: Compare Data of Today to Yesterday

Hey,

Im attaching the example. i tried all of the suggestion so far and non of them work.

You can see that in the example, if you choose the 2nd of Jan 2010 you should see in the blue box "1" but you see "2".

Any help will be appreciated.

Thanks!

Zohar.

• ###### Re: Compare Data of Today to Yesterday

Hi Zohar,

In your table don't have any field "Day". So you cannot use the field "Day" in set analysis. That why you got the wrong result.

Solution:
Change "Day" in set analysis to "TheDay".
It look like this = SUM({<TheDay={\$(=Max(TheDay-1))}>} Cost)

See the attached file.

Regards,
Sokkorn

• ###### Re: Compare Data of Today to Yesterday

Hey,

No - This is an example. Instead of the word "Day" I used "Date". "Date" is what you should look at. in my original workbook i have "Day" as a field which means "Date" (in this workbook).

"The Day" is only a field that contains the number of 1-31 for the days of the month.
So your solution only works if you are inside the month, but when choosing the 1st of the month you get zero.

I want to get the day before the day selected no matter what, even if i select the 1st.

We need to use "Date". you can see that field exists in the table, just isn't shown in my file.

Any suggestion will be appreciated.

Thanks!

Zohar.

• ###### Re: Compare Data of Today to Yesterday

Zohar

If you have other calendar fields linked to your Day field, you need to override them in the set expression because selecting Day will limit them and one or more of them may be preventing the sum from working.

=sum ({<Day ={"=\$(=Date(Max(Day)-1))"}, Weekday=, Week=>} Cost )

Hope this helps

Jonathan

• ###### Re: Compare Data of Today to Yesterday

Also Jonathan, thanks for the reply, i tried what you said but with no luck :-(

• ###### Re: Compare Data of Today to Yesterday

Zohar

I used this on your example and it seems to work correctly:

=Sum({<[Date]={"\$(=Date(Max([Date])-1))"},TheYear=,TheMonth=,TheWeek=,TheDay=,Quarter=>} Cost)

Regards

Jonathan

• ###### Compare Data of Today to Yesterday

Yay!! Finally :-)

I would love an explanation of what i did wrong or why this answer works... i tried everything... or so i though!

BTW, i can't open the file you sent to me, it tells me that my personal edition has exceeded the limit of opening other people's files, but i copied what you did and it worked... is there any way to go around the limit of files?

Thanks a lot!!!!!!

Sitting on this for days now!

Zohar.

• ###### Re: Compare Data of Today to Yesterday

Zohar

You're welcome.

zoharpos wrote:

BTW, i can't open the file you sent to me, it tells me that my personal edition has exceeded the limit of opening other people's files, but i copied what you did and it worked... is there any way to go around the limit of files?