2 Replies Latest reply on Jan 25, 2019 8:01 AM by Sergio Diniz

Good Morning!

We have a panel containing 3 spreadsheets, all 3 of which have Year filter, until everything OK.
The problem is the following, in the first worksheet, there are 3 measures:

1. Open;
2. Closed;
3. Balance.

The Balance is the calculation of the "Closed" except the "Open" and the same is calculated month by month.
For the dates (Year and Month), I am using the dimension "Period". When I put the "Year" in the filter and select all, the balance performs the calculation correctly as an example below:

Select all year:

Balance January/2019 = 18 (balance Dec/2018) + 97 (open Jan/2019) - 81 (closed Jan/2019) = 34

Selecting 2019 only:

Balance January/2019 -> 97 (open Jan/2019) - 81 (closed Jan/2019) = 16

Note that he disregarded the December / 18 balance.

To fix the problem, I searched the forums and found a solution that creates a measurement with the calculation below:

[Accumulated] = WINDOW_MAX (ATTR (DATENAME ('year',)))

I created the field and applied it to the worksheet as a filter and using "Horizontally" panel calculation.

Look at the result:

Select all year:

Selecting 2019 only:

Note that the value of the balance is now correct (using the December balance).

As I mentioned above, I use 3 spreadsheets that should be filtered by the "Year":

Since I am using the calculated filter based on the field [Accumulated], it does not have the option to "apply to all worksheets":

I need you to change the year, the other two worksheets will be updated.

I am sending the panel with the data.

Note: the first and second use the same database.

Thank you.

Sergio Diniz

• 1. Re: Aggregation filter for other spreadsheets

Could you extract only sufficient amount of sample data before creating twbx.

From the explanations, your data structure is not supporting your expected behavior and you need to pivot data to get this.

Which means kind of big re-construction is needed...

one old example.

Thanks,

Shin

• 2. Re: Aggregation filter for other spreadsheets

Shin, thanks for the reply. I am attaching the report with sufficient data. In the meantime, I'll search the link you submitted.