sum({<[Process Date]={"$(=date(max([Process Date])-1,'MM/DD/YYYY'))"}>}[Sales Posting Amount USD]) is working for me

[ananyaghosh]

Hi,

I am using the below expression:

sum({<[Process Date]={"$(=date(max([Process Date])-1,'MM/DD/YYYY'))"}>}[Sales Posting Amount USD])

for previous day calculation. But I get 0. Please tell me what wrong in it.

my [Process Date] format is MM/DD/YYYY

I have modified my code and chart functions and have attached it for reference.

Please help me.

[ananyaghosh]

Can u provide me whole example QVW file?

[maxgro]

like this?

on the left curr month sales, oct-2014 (from the start of the month) vs previous month sales sep-2014

on the right a chart to check the previous month sales sep-2014

I added this at the end of your script to modify the curr / prev month calculation

RENAME Table [As-Of Calendar] to tmp;

[As-Of Calendar]: 

NoConcatenate LOAD

  *,

  IF(MonthDelta=1 and Day([Order Date]) <= Day(AsOfDate), 'Previous Month',

  IF(MonthDelta=0 and Day([Order Date]) <= Day(AsOfDate), 'Current Month')

  ) as FlagMonth2     /* ??? 30, 31, february */

  ;

Load

  *,

  (Year(AsOfDate)*12 + Month(AsOfDate)) -  (Year([Order Date])*12 + Month([Order Date])) as MonthDelta

Resident tmp;

DROP Table tmp;

[ananyaghosh]

Hi,

Thank you much for your help and your solutions works for current month and previous month, but for week and year I am facing problem.

I have slightly modified your script  and have uploaded my updated QVW document.

If anything is wrong in expression and dimensions please let me know.

Please help me for the last time.

Thanks,

Sandip

[ananyaghosh]

Hi,

Can you help me for  week and year calculations as I am facing problem with those too.

I have slightly modified your script  and have uploaded my updated QVW document.

If anything is wrong in expression and dimensions please let me know.

Please help me for the last time.

Thanks,

Sandip