Make Filter not applicable for Running Sum

[Bilal_Shakir]

I have calculated the running sum

Now, I want to apply filter on the period to show only this:

But, when I apply the date filter, running sum also changes and I see this:

I want the filter to be applicable only on the actual value and not on the running sum. Is it possible currently in quicksight?

[Max]

I don’t believe this is possible with a calculated field.

I would suggest making your running sum a field in your dataset with SQL. Then you can just reference that field in your table and not have to make it an aggregation / running sum.

[gillepa]

@Bilal_Shakir

There is actually a way to do it. It can be done using calculated fields at both the data prep and UI layers. Max’s suggestion is still valid, but if you want to give it a try here is how it works:

I have a date (my date is EFX_EXTRACT_DATE) and a measure (EFX_LOAMOUNT). As you can see from the tables below, the first table with 3 rows (last 3 days in my dataset) shows the correct running sums for each of those last 3 days:

First Step:
At the data pre layer, before you ingest data into Quicksight, create two calculated fields:

Basically, the first one assigns the same value (the day before the last date in my dataset) to all the dates BUT the last day. The second one, assigns the same value (two days before the last day in my data set) to all dates but the last Two days in my data set. It is important to note that these two new calculated fields have only 1 value (and nulls) so when we will use them later to partition a sumOver, the partition will be done by one value only across the entire column.

Second Step
Then Publish and Save. In the UI layer, create a calculated field like this:

ifelse(

{EFX_EXTRACT_DATE}=maxOver({EFX_EXTRACT_DATE},, PRE_FILTER), sumOver(({EFX_LOCAMOUNT}), , PRE_FILTER),

{EFX_EXTRACT_DATE}=maxOver({MaxDay_1},, PRE_FILTER), sumOver(({EFX_LOCAMOUNT}), [{MaxDay_1}], PRE_FILTER),

{EFX_EXTRACT_DATE}=maxOver({MaxDay-2},, PRE_FILTER), sumOver(({EFX_LOCAMOUNT}), [{MaxDay-2}], PRE_FILTER),

NULL

)

Add this new field to the visual. Then add your date as a filter to the visual. Select Top and Bottom for filter type. Enter 3 as the numbers of values to be returned.

In Order by select your Date and MAX as the aggregation

Apply and Voila!

Let me know how it works for you.

I am marking my reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

[Bilal_Shakir]

Hi @gillepa Thanks for the detailed solution, but I think it will work only for the last 3 periods. I want the user to be able to filter any period they want, and for that it will not work. I will have to use Max’s suggestion.

[gillepa]

@Bilal_Shakir

there is actually a simple and elegant solution:

Here is how the end results looks:

Steps:

1. Create a Pivot Table and Add Date (most recent dates first)

2. Add the measure (in my case Revenue)

3. Create a calculated field (runningSum) to calculate the running Sum and add it to the pivot table

   runningSum(
   sum(Revenue)
   , [{DATE} ASC]
   )

4. Create another calculated field (ShowOnlyTopNRows):

   rank(
   [runningSum DESC]
   )

   The trick is to use a table calculation which will not FILTER the data, but the view only.

5. Add this new calculated field to the visual

6. create a filter using this new calculated field and use a parameter (less than) to control how many recent days you want to see.

7. Then you can HIDE the last column (ShowTopN) from view
   

I hope this is the solution you were looking for!

GL

[MKuhns01]

Creating a new calculated field as a date hider using a table calculation like so:

minOver(min({date}), [{date}])

and then filtering on this new calculated date field might be a better solution in this case.

It would allow the user to filter for any specific date time period, instead of being limited by only being able to look back in time.

I’ve read about it in this post:

How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed. Write a calc like this: minOver(min({order date}), [{order date}]) And then put your filter on that. Should work! Before: [image] After: [image]