Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
First, make sure that your dates are interpreted as dates and stored as dual:
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:
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)
sum({$ <Product={'XF'}, Substatus={'On hold'}, Date={'$(=date(max(Date)-1))'}>} Sales)
Thanks, but that didn't do it.
First, make sure that your dates are interpreted as dates and stored as dual:
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:
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)
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?
Num(Sum ({<Product={'XF'}, Substatus={'On hold'}, Date={"$(=max([Date])-1))"}>}Sales), '# ##0','.',' ')
Thanks a lot!