Compare Data of Today to Yesterday

[#1]

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)

Can you please help me?

thanks!

Zohar.

[Sokkorn]

Hi Zohar,

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

Hope this help.

Regards,

Sokkorn

[swuehl]

Try

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

Stefan

[#4]

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

[Sokkorn]

Hi Zohar,

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

Regards,

Sokkorn

[jonathandienst]

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

[#7]

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.

[Sokkorn]

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

[#9]

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.

[#10]

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