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

    Aggregation filter for other spreadsheets

    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