Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
SonOfJeffGoldblum
Contributor III
Contributor III

Pivot table display MTD & YTD

successfully added MTD but trying to add YTD to same Pivot Table.

Currently have not found a viable solution

Measure:

SUM(
{<[EFFECTIVEDATE.autoCalendar.InYTD]={"$(=YEAR(TODAY())-1)","$(=YEAR(TODAY()))"}
,MONTH
,WEEK
,DATE={"=SETDATEYEAR(DATE,YEAR(TODAY()))>=YEARSTART(TODAY()) AND SETDATEYEAR(DATE,YEAR(TODAY()))<TODAY()"}
>}
NORMALBALANCE)

 

added YTD flag in calendar script

If( $1 - YearStart($1) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,

 

Tried max date - max date of previous year

 

MTD set as single measure: 

sum(NORMALBALANCE) - AFTER(SUM(NORMALBALANCE))

 

want to see

for every month on current year, comparison of month over month and YTD comparison

 

Many thanks

Everything not saved will be lost
— Nintendo Quit screen message
Labels (4)
1 Solution

Accepted Solutions
SonOfJeffGoldblum
Contributor III
Contributor III
Author

Separated Yr-Mth into two dimensions, one for Year and one for Month.

For MTD, used After() for comparison

For YTD, used Last() for comparison

Everything not saved will be lost
— Nintendo Quit screen message

View solution in original post

5 Replies
sunny_talwar

Not sure I am clear as to what you are looking to do. Would you be able to share some sample data with the expected output based on the sample shared/

SonOfJeffGoldblum
Contributor III
Contributor III
Author

 
Everything not saved will be lost
— Nintendo Quit screen message
SonOfJeffGoldblum
Contributor III
Contributor III
Author

@sunny_talwar  et. al

A little more background as I am finding:

Only month-end dates are available.

Yr and Mth are individual dimensions [EFFECTIVEDATE.autoCalendar.Year] & [EFFECTIVEDATE.autoCalendar.Month].

I have made a Master Calendar and As Of Table

In sample file attached, the ytd - NOW columns are calculated with the below, but I have not gotten any further. Several solutions I have tried that I have read of your solutions or others, I can get the values individually, but when subtracting from one another, I get nulls. That is even when including year=,quarter=,month=,date= or using yearmonth, year, month for the effectivedate field as a dimension.

=ABOVE(Sum({<EFFECTIVEDATE={"=SetDateYear(EFFECTIVEDATE, Year(Today())) >= YearStart(Today(),0,12) and SetDateYear(EFFECTIVEDATE, Year(Today())) <= Today()"}>} NORMALBALANCE))

-

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}NORMALBALANCE)

Everything not saved will be lost
— Nintendo Quit screen message
SonOfJeffGoldblum
Contributor III
Contributor III
Author

@sunny_talwar 

Below i added sample data as requested. Do you have an idea on how to display this across all months of current year against max date of last year. Then the same for all of last year against previous year?

Based on the data we have, there is only month-end data per year-month which does make it difficult in expressions that ask a specific date per month.

I have not been able to find a single formula that will help outside of performing and loading manually.

Thank you in advance

Everything not saved will be lost
— Nintendo Quit screen message
SonOfJeffGoldblum
Contributor III
Contributor III
Author

Separated Yr-Mth into two dimensions, one for Year and one for Month.

For MTD, used After() for comparison

For YTD, used Last() for comparison

Everything not saved will be lost
— Nintendo Quit screen message