yesterday's sales

[#1]

Hi Experts,

I m trying to calculate yesterday sales value in a textbox.

My expression is

num(sum({<[Full Date]={'$(=max(date([Full Date]-1)))'},[DeliveryFlag]={1}>} Sales),'#,##0')

but i m getting 0. is anything wrong in my expression?

can someone please help?

[Gysbert_Wassenaar]

Put the date() function around the max() function and make sure to use the correct date format.

num(sum({<[Full Date]={"$(=date(max([Full Date]-1),'MM/DD/YYYY'))"},[DeliveryFlag]={1}>} Sales),'#,##0')

MM/DD/YYYY is just an example. Use whatever format the field [Full Date] actually has.

[#3]

thank you gysbert.

can you please help me on WTD, MTD & previous YTD expressions?

[Gysbert_Wassenaar · Accepted solution]

WTD:  {<[Full Date] = {">=$(=Date(WeekStart(Max([FullDate])),'DD/MM/YYYY')))<$(=Date(Max([FullDate]),'DD/MM/YYYY'))"} >}

MTD:  {<[Full Date] = {">=$(=Date(MonthStart(Max([FullDate])),'DD/MM/YYYY')))<$(=Date(Max([FullDate]),'DD/MM/YYYY'))"} >}

YTD:  {<[Full Date] = {">=$(=Date(YearStart(Max([FullDate])),'DD/MM/YYYY')))<$(=Date(Max([FullDate]),'DD/MM/YYYY'))"} >}

[sunny_talwar]

Shouldn't we use WeekStart for WTD?

WTD:  {<[Full Date] = {">=$(=Date(WeekStart(Max([FullDate])),'DD/MM/YYYY')))<$(=Date(Max([FullDate]),'DD/MM/YYYY'))"}>}

[Gysbert_Wassenaar]

Yeah, you're right.

[#7]

gysbert,

this expression is working only when a date is selected.

i want this to be displayed even there is no date selection.

[#8]

sunny,

this is not giving required value.

i cant see anything wrong in the expression.

[Gysbert_Wassenaar]

The expressions can't be used in a table where you use Full Date as dimension. For that you need to create an As-Of table in the script to create a new field that can be used instead as chart dimension. See this blog post: The As-Of Table. Only instead of using months as periods you need to use days. Every day has to be linked with every day before it.

[#10]

i found the error gysbert.

it was the misplace of round bracket.

its working fine.

thank you so much