Set analysis - compare data of today to yesterday?

[annanirvin]

Hi.

I'd like to sum the number of orders with certain preferences from the day before my chosen day, e.g. if I choose 11/10/2016 I'd like to sum the number of orders 11/09/2016. I've tried a number of different ways to do this, unfortunately without success. This is my latest try:

=Num(Sum ({<Product={'XF'}, Substatus={'On hold'}, Date={"$(=Date([Date])-1))"}>}Sales), '# ##0','.',' ')

Anyone who can help me, please?

Thanks!

Anna

[maxgro]

sum({$ <Product={'XF'}, Substatus={'On hold'}, Date={'$(=date(max(Date)-1))'}>} Sales)

[annanirvin]

Thanks, but that didn't do it.

[swuehl · Accepted solution]

First, make sure that your dates are interpreted as dates and stored as dual:

Get the Dates Right

Why don’t my dates work?

Then you can use a dollar sign expansion with expression to calculate the previous date, but you might need to reformat the result to match the field format:

Dates in Set Analysis

if your field format differs from the default, you need to specify it, e.g.

sum({$ <Product={'XF'}, Substatus={'On hold'}, Date={"$(=Date(Max(Date)-1,'MM.DD.YYYY'))"}>} Sales)

[maxgro]

To check your espression:

- Put the expression into e.g. a straight table chart and leave the expression label empty.

- Hover with the mouse over the expression header in chart (as in image)

- You should see the expression with the dollar sign expansion evaluated and replaced.

- What do you see?

[Anonymous]

Num(Sum ({<Product={'XF'}, Substatus={'On hold'}, Date={"$(=max([Date])-1))"}>}Sales), '# ##0','.',' ')

[annanirvin]

Thanks a lot!