4 Replies Latest reply on Jul 24, 2018 2:37 PM by Stephan Jones

    Using Index and Parameter to dynamically change months

    Stephan Jones

      Hello,

       

      I am trying to build a view that will show at least the current month and previous month that can dynamically change with a parameter as a filter (current/previous month). I have another data source that I can do this with but my 2nd data source that's shown on the dashboard need to be able to show the change from the previous month with directional arrows. If I only had a few categories, i'd just build it out in several views and combine on a dashboard, however with many categories, I don't want someone to sort and cause an issue.

       

      I was playing around with using index, but run into a problem of wanting to select multiple index values when the statement is true.

       

      I've attached a sample workbook with a little guidance to where i'm going....

        • 1. Re: Using Index and Parameter to dynamically change months
          Jim Dehner

          not certain I full understand

           

          Are you trying to use the parameter to set number of months that you want to do the variance over   or the starting point for a MoM?

           

          in either event you could change the parameter to

           

           

          then you can change the  index filter to

          and the difference formula to this

           

           

          or it could be

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Using Index and Parameter to dynamically change months
            David Maning

            Hi Stephan,

             

            Here is another way how to deal with your issue. You can use another table calculation:

             

            IF DATEDIFF('month', WINDOW_MAX(MAX(DATETRUNC('month', [Order Date]))), ATTR(DATETRUNC('month', [Order Date]))) = 0

            THEN 'current month'

            ELSEIF DATEDIFF('month', WINDOW_MAX(MAX(DATETRUNC('month', [Order Date]))), ATTR(DATETRUNC('month', [Order Date]))) = -1

            THEN 'previous month'

            END

             

            Trust this helps.

            D

            • 3. Re: Using Index and Parameter to dynamically change months
              Bryce Larsen

              So, first I think you should define a "report date", which would be the MAX date within your data. This could either be TODAY() if your data is updated daily or you can calculate it using {MAX([Date])}. I'll use the latter for the example. In this case I'm truncating it to the month so that's all we care about:

              Next I changed the parameter to actually be an integer in the backend, where current month is 0 and previous month is -1. I then use this to bring in the most recent 2 months based on the parameter:

              Now you have both months in your viz, but you want to hide when MonthCD = -1:

               

              Then you can use functions similarly as you were:

              Current:

              Previous:

               

              Attached a 10.3 twbx here Hope this is of some use to someone!

               

              Best,

              Bryce

              • 4. Re: Using Index and Parameter to dynamically change months
                Stephan Jones

                Let me clarify,

                 

                So if I have the filter on Current Month, I want to see months 1,2,13 (Current, previous, previous year)

                 

                If I have the filter on Previous Month, I want to see months 2,3,14

                 

                I found another way to do it, but using index helps with some other items I have (one of those - which is easier to rework things).