4 Replies Latest reply on Aug 8, 2018 9:53 AM by Robertino Bonora

    Action filter for different months

    Robertino Bonora

      Hey, guys, I have the following question,

       

      I have two graphs, one is a table that shows the sum of records per transaction in the rows and in the columns I have the current month and the previous month. In another line graph, it shows the sum of the records for the current month (it is open at a day level), and the previous month, as shown below

       

       

      I have an action filter in which if I select one or more transactions in the table, the line graph is filtered, this is OK and works correctly. Additionally I have another action filter that filters the table if I select a point in the rows (each point represents a date), so if I select for example 2018/08/05 as in the next image, the action filters the whole table so that the Current month column shows the data for that specific date and the Previous month column shows 0 in the whole column. My question is, is it possible to select (using the same example above), the date 2018/08/05 so that in the table of the current month column filters the data with that date selected AND filter the previous month's column with the same day of the selected date but for the previous month? and of course the same case applies if I select a date of the previous month, the current month's column has to filter with the same day of the selected date but for the current month....

       

       

      Months/Years are selected using parameters.

       

      I attached to images with the configuration of two sheets, in the table sheets the measures are:

       

      Txn_Mes_Actual_Parametro:

                                                        IF MONTH([Date])=[Month] AND

                                                            YEAR([Date])=[Year] AND

                                                            [Date]<={[Max_Current_Month]}

                                                        THEN

                                                                 [CountRecords]

                                                        END

       

      Txn_Mes_Anterior_Parametro:

                                                        IF MONTH([Date]) = MONTH(DATEADD('month',-1,MAKEDATE([Year],[Month],1))) AND

                                                            YEAR([Date]) = YEAR(DATEADD('month',-1,MAKEDATE([Year],[Month],1))) AND

                                                            [Date] <= { [Max_Previous_Month] }

                                                        THEN

                                                                 [CountRecords]

                                                        END

       

      In the line chart the measure is:

       

      Cantidad_Transacciones_Meses:

                                                                  IF

                                                                      ([3_Meses_Variables]) = 'True'

                                                                  THEN

                                                                      [CountRecords]

                                                                  END

       

      with 3_Meses_Variables being:

                                                                  IF      (([Date] <

                                                                                    DATEADD('month',1,DATE(STR([Year])+'-'+STR([Month])+'-01'))

                                                                           AND     [Date] >=

                                                                                    DATE(STR([Year])+'-'+STR([Month])+'-01'))

                                                                      OR

                                                                          ([Date] <

                                                                                    DATE(STR([Year])+'-'+STR([Month])+'-01')

                                                                           AND     [Date] >=

                                                                                    DATEADD('month',-1,DATE(STR([Year])+'-'+STR([Month])+'-01')))

                                                                  THEN

                                                                                     'True'

                                                                  ELSE

                                                                                     'False'

                                                                  END

       

       

      I hope I made myself understood.... I can't publish the.twbx file because of security issues,

       

      Robertino

        • 1. Re: Action filter for different months
          Shinichiro Murakami

          HI Robertino,

           

          In this case, you need to duplicate data anyways.

          I used union with using very simple sample.

           

           

          From edit data source.

           

           

           

           

           

           

           

           

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Action filter for different months
            Robertino Bonora

            Thanks Shinichiro! im gonna try this solution, in my case is not simple to duplicate data but im going to try it thanks!

             

            Robertino

            • 3. Re: Action filter for different months
              Shinichiro Murakami

              Hi Robertino,

               

              You are welcome.

              Anyways, the point is dashboard action filter filters data itself, so you need to prepare another data to make relationships between current and previous.

              Kind of cheating the calculation logic.

               

              Thanks,

              Shin

              • 4. Re: Action filter for different months
                Robertino Bonora

                Shinichiro hello, if you read my description I told you that I have parameters to choose the year and months, and the table I'm trying to filter from the line graph, already has the two fields calculated in the columns:

                 

                Txn_Mes_Actual_Parametro:

                                                                  IF MONTH([Date])=[Month] AND

                                                                      YEAR([Date])=[Year] AND

                                                                      [Date]<={[Max_Current_Month]}

                                                                  THEN

                                                                           [CountRecords]

                                                                  END

                 

                Txn_Mes_Anterior_Parametro:

                                                                  IF MONTH([Date]) = MONTH(DATEADD('month',-1,MAKEDATE([Year],[Month],1))) AND

                                                                      YEAR([Date]) = YEAR(DATEADD('month',-1,MAKEDATE([Year],[Month],1))) AND

                                                                      [Date] <= { [Max_Previous_Month] }

                                                                  THEN

                                                                           [CountRecords]

                                                                  END

                 

                And Max_Current_Month is:

                                                             MAX([Date])

                 

                Max_Previous_Month:

                                                   IF ISNULL(MAX(IF YEAR([Fecha])=[Año] AND

                                                          MONTH([Fecha])=[Mes]

                                                   THEN MAKEDATE([Año],[Mes],DAY([Fecha])) END)) = TRUE

                                                        THEN MAX(IF YEAR([Fecha])=[Año] AND

                                                            MONTH([Fecha])=[Mes]-1

                                                   THEN  MAKEDATE([Año],[Mes]-1,DAY([Fecha])) END)

                                                   ELSE    DATEADD('month',-1,MAX([Fecha]))

                                                   END

                 

                If you look I am using parameters to show the current and previous month, something like what you sent me except that you calculate the Current and Previous Value with this formula if ([Table Name]) = "Sheet1$" then ([Value]) else 0 end

                 

                How can I use my two calculated fields Txn_Month_Current_Parameter and Txn_Month_Previous_Parameter that are based on parameters to show the selected months and additionally when I filter a day in the line diagram this filter uses its calculation logic of the same day of the previous/next month as the case may be....

                 

                I hope you understand me and there is a way to resolve this very particular issue.... Thank you! Thank you!