Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Convert YTD to MTD (cumulative to non cumulative)

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

   

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?

vamsee
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.