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

Roll up to 1st date

Hi,

   

DateCol ACol BCol CMTD
01-01-2017522125%
02-01-201735473%

03-01-2017

442

85%

In the above table the MTD column logic for 01-01-2017 is : Col A /( Col B + Col C) i.e 5 / (2+2)

Logic for 02-01-2017 is : sum(Col A) / (sum(Col B) + Col C)  i.e (5+3) / ( (2+5) + 4)  and this roll up continues for the rest of the dates. Also the roll up should be restricted to the 1st of every month.

Can someone please tell me the set analysis to achieve the same in the front end?

4 Replies
sunny_talwar

May be like this

RangeSum(Above(Sum(ColA), 0, RowNo())) / (RangeSum(Above(Sum(ColB), 0, RowNo())) + Sum(ColC))

OmarBenSalem

If you want to have in a table :

RangeSum(Above(Sum({<date>}[Col A]), 0, RowNo())) / (RangeSum(Above(Sum({<date>}[Col B]), 0, RowNo())) + Sum({<date>}[Col C]))

If you want to have a KPI object, and when you select a date, you have your calculation:

sum({<date=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}[Col A])

/(sum({<date=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}[Col B])

+

aggr(sum([Col C]),date))

Result:

Capture.PNG

OmarBenSalem

Hi sunny, what If I wanted to add Col A, Col B and Col C as dimensions in the table?

That would affect the output, how should we change the expression in this case?

Capture.PNG

sunny_talwar

In that case you need a total

RangeSum(Above(TOTAL Sum(ColA), 0, RowNo(TOTAL))) / (RangeSum(Above(TOTAL Sum(ColB), 0, RowNo(TOTAL))) + Sum(ColC))