Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Valued Contributor

Convert YTD to MTD (cumulative to non cumulative)


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.




2 Replies

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

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

Valued Contributor

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

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.