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:

      imagem1.jpg

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

       

      Selecting 2019 only:

      imagem2.jpg

       

      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:

      imagem3.jpg

      Selecting 2019 only:

      imagem4.jpg

       

      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":

      imagem5.jpg

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

      imagem6.jpg

      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