Using Recurrence Formulas in a Sorted Matrix

3.17K viewsGeneral Discussion
0

Hi,
I have a Matrix populated with data using DataNav. When DataNav pulls the data in (say a list of customers as the category and sales as the data “item”), it is brought in based on the data source’s original order. I’ve sorted it by descending sales order. Now I want to look at a running total using:

Cumulative Rev = Sum(Revenue:CustName[FIRST] .. Revenue:CustName[THIS])

But the formula respects the original sort order (the data sources), not the one I created (descending by sales).

I must be missing something. Any help is greatly appreciated.

Thanks,
Rich

0

Hi.
Maybe you can order by the revenue column in the SQL query in datanav, then use your recurrence formula.
Otherwise you may try an alternative formula such as
sum(selects(revenue:custname;revenue:custname;”<=” & revenue))
It should add for each row the revenues less than or equal to the one in the row, unrespective of their physical order. If there aren’t two rows with the same revenue value it should work.
Bye

You are viewing 1 out of 1 answers, click here to view all answers.