0 Replies Latest reply on Aug 3, 2018 2:58 PM by Robertino Bonora

    Action filter in 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