10 Replies Latest reply on Sep 9, 2016 1:12 AM by Jonald Bacay

    How to use parameter to filter Date/Period

    Jonald Bacay

      Hi Guys,

       

      Need some urgent help here. To make it simple, I managed to have a parameter below:

           Actual vs Plan

           Plan vs Plan

           Actual vs Actual

      I created a calculated field using if for the amount to show when user select either of above parameters (

      Amount 1    

           if [Parameters]="Actual vs Actual" then [Actual Bookings]

           elseif [Parameters]="Plan vs Plan" then [AG PLAN 2016 (2016_Plans)].[Plan Booking]

           elseif [Parameters]="Actual vs Plan" then [Actual Bookings]

           end)

       

      Amount 2

           if [Parameters]="Actual vs Actual" then [Actual Bookings]

           elseif [Parameters]="Plan vs Plan" then [AG PLAN 2016 (2016_Plans)].[Plan Booking]

           elseif [Parameters]="Actual vs Plan" then [AG PLAN 2016 (2016_Plans)].[Plan Booking]

           end

       

      Then put both the amount in the worksheet. Both are working until i have to do another parameter to filter the Period this time.

      I created a Period parameter such as:

      Q1 vs Q2, Q1 vs Q2, Q1 vs Q3..and so on...I actually not sure on this approach coz i need to have a long list to have all the quarter combinations added to the list plus the year of course.

      The idea is, the user have an option to select which period he want to compare after he selected his first parameter. Then the two calculated field (amount1 and amount2) will refresh based on that period.

       

      appreciate helps on this.

       

       

      thanks

        • 1. Re: How to use parameter to filter Date/Period
          chris.moore.11

          Its hard to say without seeing your workbook. What you could do is create two parameters two choose the two dates you want to compare, then create two calculated fields based on those selections.

           

          Basically...

          if DATENAME('year',[Date]) = DATENAME('year',[Parameter 1]) and DATENAME('quarter',[Date]) = DATENAME('quarter',[Parameter 1]) then [Feild] else null end

          and...

          if DATENAME('year',[Date]) = DATENAME('year',[Parameter 2]) and DATENAME('quarter',[Date]) = DATENAME('quarter',[Parameter 2]) then [Feild] else null end

           

          See the attached workbook for an over simplified illustration. I dont know how it fits into your already calculated fields though, but you could probably use this idea.

           

          Hopefully this helps.

          • 2. Re: How to use parameter to filter Date/Period
            Akriti Lal

            You can create a Boolean Calculated Field based on Order Date Parameter and add it to your visualization and provide the parameter value as you would like to have it .

             

            Let me know if there is something else you are looking for.

             

            Thanks

            • 3. Re: How to use parameter to filter Date/Period
              Jonald Bacay

              Hi Chris,

               

              Thanks for your assistance,

               

              I attached the sample workbook for your easy reference. What I did is, I already created a parameter to show my value dimension (actuals or plan)

              Now, my second parameter (period) should also filter my result whether Q1 vs Q1 and so on.

               

              Thanks and appreciate your help

              • 4. Re: How to use parameter to filter Date/Period
                Jonald Bacay

                HI Akriti,

                 

                Thanks for the assistance. I attached the sample workbook that I am working on. I managed to have the revenue amount appear based on amount paramenter (actuals or plan). But I should also have another parameter for Period (Q1 vs Q1 so on) which will then filter the value on my first parameter.

                 

                Appreciate your assistance

                 

                 

                thanks

                • 5. Re: How to use parameter to filter Date/Period
                  Jonald Bacay

                  Hi Chris,

                  I just reply directly from my email as I do not know to insert the workbook here...?

                  • 6. Re: How to use parameter to filter Date/Period
                    chris.moore.11

                    Hi, the attached workbook has two sheets. The first is my suggestion on a way to approach it, the second is a solution to the way you were headed towards.

                     

                    The first sheet has two parameters for each "comparison group". The user selects actual/plan and the time period for the first group, then the same for the second. See the calculated fields "Compare 1" and "Compare 2" that use some parameters i created. Doing it this way is much more scale-able and doesnt require logic for every single comparison option.

                     

                    The second sheet uses the way you were headed and just embeds more logic into the field you created. See the calculated fields "Actuals Par_1 (copy)" and "Actuals Par_2 (copy)" that should respond the the period parameter you created.

                     

                    Hopefully this helps.

                    • 7. Re: How to use parameter to filter Date/Period
                      Jonald Bacay

                      Awesome Chris. I will give a shot your suggestion tab. I think that’s the most scalable approach. I will let you know if I got it working

                       

                      thanks

                      • 8. Re: How to use parameter to filter Date/Period
                        Jonald Bacay

                        Hi Chris,

                         

                        I really getting it hard, but almost there (perhaps).

                         

                        It’s giving the result format that it should not. Maybe due to aggregation coz I have to aggregate the values (as said by error). I don’t need the “quarters” in my column coz that will be obvious based on the parameter (date 1 & date 2). But when I removed quarters from column, I am not getting any value????.

                         

                         

                         

                         

                        No value:

                         

                         

                         

                        The view should be like this (the one you sent):

                         

                         

                        I attached the actual workbook that I am working on. Appreciate your help.

                         

                         

                        thanks

                        • 9. Re: How to use parameter to filter Date/Period
                          chris.moore.11

                          Hi, since you now have two data sources it got a little tricky.

                           

                          You we're close but your problem is with the attr() function. That function works on a set and returns a value if all those values are the same, otherwise it returns "*". The reason your workbook works when you have the quarter in the columns, is because it then groups them so they will all have the same result, and the attr() function works as you were hoping. When you remove that time dimension from the columns then there are multiple quarters within that group so it will return "*" and your visualization will break.

                           

                          To fix it, you can create an amount 1 and amount 2, for each data source that has logic from your date parameters. Then you will create a field for amount 1 and 2 that has logic to choose either the plan or actual based on your parameters.

                           

                          See these calculated fields attached.

                          Amount 1 (actual)

                          Amount 2 (actual)

                          Amount 1 (plan)

                          Amount 2 (plan)

                          Amount 1

                          Amount 2

                           

                          Hopefully that helps.

                          • 10. Re: How to use parameter to filter Date/Period
                            Jonald Bacay

                            Awesome, Chris. I thought it's simple, but i made to believed, its complicated. Yet, you got it!

                             

                            thank you, thank you, thank you!!!