Week to Previous date and month to previous date

[anuradhaa]

I want to get average sales for month to previous date. and week to previous date.

I know how to do this for Month to date and week to date. i have used

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Num(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM))"}>} (sales), 0.01))

and my script i have used

Floor(mydate) AS mydate_NUM,

How can i do this for previous date

[Anonymous]

Previous date means?

Could you explore more?

The expression you have written is: week till date

Do you want Month Till date as well?

[Anonymous]

see this, might be helpful for you:

YTQ, QTD, MTD and WTD

[anuradhaa]

Hi,

When i use that it will display all the records till the reload time. but i want the records till the previous day,

say i run this at 05.00 am 2015/12/08 i want the data till 11.59 pm 2015/12/07. so that means i want to remove the records that lies in 2015/12/08

[Anonymous]

then simply minus 1 from max date like:

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Num(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

[anuradhaa]

No, That is not correct.

If it reloads at 2015-12-08 04.00 am and there are no records for 2015-12-08 then the max(mydate) is 2015-12-07.

So when we subs-tact 1 from that then it will not show previous day records.

[MayilVahanan]

Hi

Try like this

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(WeekStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(MonthStart(Max(mydate_NUM))))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

Edit:

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(WeekStart(Today())))<=$(=Floor(Today()-1)"}>} (sales), 0.01))

=(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=Floor(MonthStart(Today())))<=$(=Floor(Today()-1)"}>} (sales), 0.01))

[tyagishaila · Accepted solution]

Hi Anuradha,

You can use ReloadTime() in place of Max(mydate_NUM)

and for previous date you can use variable in script

LET vPrevDay     =    date(ReloadTime()-1);

[Kushal_Chawda]

try this

(ceil(avg({<Year=, Month=, Quarter=, Week=, mydate=, mydate_NUM={">=$(=WeekStart(Max(mydate_NUM)))<=$(=Max(mydate_NUM)-1)"}>} (sales), 0.01))

[Anonymous]

use today() instead of =Max(mydate_NUM)