Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator III

Rangesum Above with Multiple Dimensions

Hi All,

I have the below pivot table

rangesum.png

The balance is the previous weeks balance plus the previous weeks revenues which I get using the expression

RangeSum(

     Sum(TOTAL {<[Weekstart]>} Balance),

          RangeSum(Before(Sum({<[ Weekstart]>}UnsettledSell)

               , 1, ColumnNo())))

I then have appended this expression to the following for use in a line chart and it works great.

RangeSum(

     Sum(TOTAL {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

               , 1, RowNo())))

rangesum.png

The problem I am having is I want to add another dimension 'Company' to the line chart it ruins the expression and gives me the below.

rangesum.png

I believe it has to do with the above function and multiple dimensions but I am unsure on how to solve.

Thanks!!

Mark

1 Solution

Accepted Solutions
sunny_talwar

Are you sure you used this?

Aggr(

RangeSum(

    Sum(TOTAL <Company> {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

              , 1, RowNo())))

, Company, (Weekstart, (NUMERIC)))

View solution in original post

6 Replies
sunny_talwar

May be try this

Aggr(

RangeSum(

     Sum(TOTAL <Company> {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

               , 1, RowNo())))

, Company, Weekstart)

or this

Aggr(

RangeSum(

     Sum(TOTAL {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

               , 1, RowNo())))

, Company, Weekstart)

m_perreault
Creator III
Creator III
Author

Not quite working, the balances seem to be applied to the incorrect weeks.

The pivot table shows the desired results and the straight table shows what I am getting

rangesum.png

sunny_talwar

How about this?

Aggr(

RangeSum(

     Sum(TOTAL <Company> {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

               , 1, RowNo())))

, Company, (Weekstart, (NUMERIC)))

m_perreault
Creator III
Creator III
Author

Closer I think! That works great when I have one company selected, however if I select a second company the starting balance for each company becomes the sum of their two starting balances. Their revenues are then applied appropriately in subsequent weeks but the starting balance is the sum of the two companies.

rangesum.png

sunny_talwar

Are you sure you used this?

Aggr(

RangeSum(

    Sum(TOTAL <Company> {<[Weekstart]>} Balance),

          RangeSum(Above(Sum({<[ Weekstart]>}UnsettledSell)

              , 1, RowNo())))

, Company, (Weekstart, (NUMERIC)))

View solution in original post

m_perreault
Creator III
Creator III
Author

Ah I was not, it works great thank you!!