Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Only display cumulative total when condition is met

I'm building a line chart that has a cumulative total for the current and prior year (using set analysis) for the % of filled seats by the number of days to the event (where 0 is the day of the event).  For the prior year, all events have happened so the cumulative total builds until 0.  For the current year, not all events have happened so the cumulative total builds until a certain point and then shows the same number (flat lines) through day 0 since no data is available for future # of days to the event.

I don't want it to display the same cumulative value for the remainder of the current year.  Once I reach the next value for days to event that hasn't happened yet, I want the line to stop.  I do want to show all cumulative totals for the prior year.

How would I accomplish this?  Would it be a combination of set analysis and an if statement?

Here is my formula to calculate the % of filled seats (uses set analysis to isolate the current year - I have another one for the prior year):

RangeSum(Above(Total sum({$<

Year={$(=max(Year))}>}ReservedSeats),0,RowNo(Total)))

/

(Sum({$<Year={$(=max(Year))}>}

TOTAL aggr(max({$<Year={$(=max(Year))}>}AvailableSeats),EventCounter, City)))

2 Replies
Digvijay_Singh

Did you try showing missing values as gaps in the presentation properties?

Anonymous
Not applicable
Author

Since it is a cumulative total, there technically aren't missing values.  But, it is set to show missing values as gaps.