1 Reply Latest reply on Jul 13, 2018 12:45 PM by Yuriy Fal

    Aggregate data for multiple selections

    Vasu choudhury

      Hello,

       

      I would like to know if there is any workaround in displaying the values from the same data source or any other work around.

       

      Based on row level, at a given point of time, a user can access only to a single Region and cant access other regions data. But I need to consider other Regions data for 2 metrics as mentioned..

       

      Common filters are Region, City, Category & Name. Lets say X user belongs to Region-1 and Region filter will show only Region-1 value.

      Sample Data:

             

      I would like to create 2 Calc fields at month level i.e. PB & NB

       

      Scenario 1: Default selection of all quick filters..

      Expected/Logic for PB:

      Calculate the Revenue for each month who belongs to region-1.

      i.e. 310 = F2+F3; 400=F4; 0= No values for Mar in Region-1; and so on...

       

      Expected/Logic for NB:

      Calculate the Revenue & Qty for each month for all regions i.e. Region-1&2..( Even though user belongs to Region-1, in calculating NB, we need to consider all regions from the table)

      i.e. Rev 615 = F2+F3+F12      ; 750=F4+F13

           Qty 44 = G2+G3+G12        ;23 = G4+G13

            Result 13.977 = 615/44 and so on.....

       

      PB & NB results should not vary for City, Name quick filters changes. It should be constant, but should vary for Category only.

       

      Scenario 2:

      when Category=Coloring is selected then below results should be displayed.

      Scenario 3:

      when Category=Others is selected then below results should be displayed.

       

      Appreciate it!

       

      Thanks!