Convert YTD to MTD (cumulative to non cumulative)

vamsee — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Please help me with the pivot table logic for

The database has YTD cumulative values. To non cumulate the values the business logic is

for every date value subtract the previous month end value.

Eg: In order to obtain the MTD value for 8/1/2016

SUM({<date={'8/1/2016'}>}Amount) - SUM({<date= {'7/31/2016'}>}Amount)

To achieve the previous month end value the expression used is

SUM({< Date_Field={"$(=Date(MonthEnd(Max(Date_Field),-1)))"}>} Amount)

The expression to caluclate daily values is

SUM({<Year=, Month=,Date_Field ={">=$(=(YearStart(Max(Date_Field))))<=$(=Max(Date_Field))"}>} Amount)

Requirement is to display all the values from the Yearstart.

-----------------------------------------------------------------

NOTE: I have to group by 3 columns which come from a different table. (Attached sample model)

-----------------------------------------------------------------

Really appreciate your help.

Thanks

Vamsee

Re: Convert YTD to MTD (cumulative to non cumulative)

sunny_talwar — Mon, 01 Aug 2016 19:13:03 GMT

Would you be able to provide a sample or some dummy data with the expected output to help you better here?

vamsee — Mon, 01 Aug 2016 20:46:45 GMT

Hi Sunny,

Please find the sample attached Excel file.

I believe that should suffice.

Please find the attached QVW based on the Excel Data.

Thanks ! for the prompt response. Appreciate it.