Set analysis, sum yesterday

[#1]

So now I've got the below two separate working formulas, how do I put them together? My set analysis skills are poor, I never seem to learn the syntaxes

TODAYS

=SUM(IF(TYPE='XXX',AMOUNT))

BUT NEED TO USE YESTERDAYS DATE

IF(WeekDay(Max(Date)) = 'Mon', Date(Max(Date) - 3), Date(Max(Date) -1))

[mukesh24]

Use Following syntax:-

sum( {$<Date= {$(#=Max(Date)-1)}>} Amount)

Regards,

Mukesh Chaudhari

[#3]

Hi Mukesh,

can you pls specify an example with my if condition (IF TYPE) as well?

Thanks in advance,

Olle

[PrashantSangle · Accepted solution]

Hi,

Try like

for today

sum({<Type={"XXX"},DateField={"=date(Today())"}>}Amount)

or

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)))"}>}Amount)

for Yesterday

sum({<Type={"XXX"},DateField={"=date(Today()-1)"}>}Amount)

or

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

Regards

[#5]

Hi Max,

I think I need to rephrase my request. I need to sum the previous date, meaning If current date is monday I need to show the sum of fridays amounts.

Hence my formula for this purpose:

IF(WeekDay(Max(Date)) = 'Mon', Date(Max(Date) - 3), Date(Max(Date) -1))

When trying your formula below Im getting only zero values.

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

[PrashantSangle]

Hi,

Try like

if(weekday(Date)='Mon',

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-3))"}>}Amount),

sum({<Type={"XXX"},DateField={"$(=date(max(datefield)-1))"}>}Amount)

)

Regards

[#7]

still getting zero values im afraid

[PrashantSangle]

Hi,

Debug your set analysis expression

1)convert your chart to straight table.

2)Write above expression.

3)dont write label

4)Press Ok

5) Once chart created , take your arrow to header of that expression.

Check what value it is showing for Datefield=

If possible post screen shot of it.

Regards

[Anonymous]

is it just a mistake in your statement, but on the left you write "Datefield" (upper D) and in the right expression you say

max(datefield) (lower d).

Instead of using if statement and test for monday you may use

firstworkdate(datefield,1) which delivers the previous workdate

and it works for public holidays as well (normally you will have no sales on a public holiday, except your shop is opened)

[#10]

Hi Rudolf,

that is a neat solutuion, however Im doing this for different currencies/countries with different holidays.

So I need something more dynamic

Kind REgards,

Olle