cancel
Showing results for 
Search instead for 
Did you mean: 

RSUM not producing desired result when filtering

Silutions
10 - ETL
10 - ETL

I've built a widget that uses RSUM to sum the values for multiple rows/RSUM of Fiscal Period of Year as shown in column Avg YTD Revenue shown in the two screen shots below:

Capto_Capture 2023-02-15_05-58-45_PM.png

Capto_Capture 2023-02-15_05-59-22_PM.pngWorks great.  However, if I filter the pivot down to a single Fiscal Period the calculation is incorrect because the RSUM reduces to that single row.  See screen shot if filtered to period 8 below:

Capto_Capture 2023-02-15_06-02-57_PM.png

I have tried using the ALL() function, but of course that results in all periods for all time.  Anyone have any thoughts on how to achieve the goal.

Note that I can't use YTD() because these are custom fiscal periods on a multi-tenant system with different fiscal years & periods.  In doing some testing, it looks like it would have the same issue.

Any help greatly appreciated, Jim

4 REPLIES 4

Angelina_QBeeQ
10 - ETL
10 - ETL

Hi @Silutions ,

I'm not sure if this trick will help you on your server, but you can try. 
Write a formula with All() function and divide it by the unique count of the field you use in All() function.
Get something like this:

( RSUM( [Total DriverBasePay] ),all([Fiscal Period ID]) )/
( RSUM( [# of unique FiscalYearMonth] ),all([Fiscal Period ID]) )/
[# of unique Fiscal Period ID]


Always here to help,
Angelina from QBeeQ
[email protected]

Angelina,

Unfortunately what you suggest doesn't work.  It gives incorrect results whether all periods or just one period is shown.  I'll study a little more on your approach to see if I can modify to work.  Thanks for taking a look and tell Michael I said hey!

Jim 

Angelina_QBeeQ
10 - ETL
10 - ETL

@Silutions ,

Okay, you can try another option. Check if the Analytical Engine is enabled.
If so, deactivate it (https://docs.sisense.com/main/SisenseLinux/activating-analytical-engine.htm) and check the calculation with all() function.
But it takes some time for the changes to take effect.

Kind regards,
Angelina

Angelina,

Good suggestion, but we have already made the decision to leave the Analytical Engine enabled.

Regards, Jim