14 Replies Latest reply: Nov 18, 2016 3:50 PM by Sunny Talwar RSS

    Expression Needed

    Kathy Parker

      I have a field called Queue names and a field called Queue Calls.  I need the sum of queue calls when the queue name is "Billing Q".  How would I write that expression?

        • Re: Expression Needed
          Marcus Sommer

          Try:

          sum({< [Queue names] = {'Billing Q'}>} [Queue Calls])

           

          - Marcus

            • Re: Expression Needed
              Kathy Parker

              For some reason, the results are return the value x 7 (I have a total of 7 queues). 

               

              My answer should be 5117 but, instead it is 35819.

                • Re: Expression Needed
                  Sunny Talwar

                  Make sure the name of the field match case-to-case. Is the field called Queue Names or Queue names?

                    • Re: Expression Needed
                      Kathy Parker

                      thanks!! That worked.   Now... if I want to add set analysis to populate just the current year's values,  what would I add to this expression?

                        • Re: Expression Needed
                          Sunny Talwar

                          Like this:

                           

                          Sum({<Queue = {'Billing Q'}, Year = {$(=Max(Year))}, Month, MonthYear, Quarter, QuarterYear, Week, Date>} [Queue Calls])

                           

                          All the fields in red are the other date related fields where you might want to make selection but would not want your expression to be impacted with. So, if you add them in your expression, all the selections will be ignored in those fields.

                            • Re: Expression Needed
                              Kathy Parker

                              Sunny, Awesome!  So this will pull in 2016 or the current year.  What do I use for adding a measure for the previous year? (Thank you for your help!)

                                • Re: Expression Needed
                                  Sunny Talwar

                                  Previous year should be like this

                                   

                                  Sum({<Queue = {'Billing Q'}, Year = {$(=Max(Year)-1)}, Month, MonthYear, Quarter, QuarterYear, Week, Date>} [Queue Calls])

                                    • Re: Expression Needed
                                      Kathy Parker

                                      To pull back current month and year, I tried this

                                       

                                      Sum({<[Queue Name] = {'Billing Q'}, Year = {$(=Max(Year))}, Month = {$(=Max(Month))}, Date>} [Queue Calls])

                                       

                                      For Previous month, I would think I could try this:

                                      Sum({<[Queue Name] = {'Billing Q'}, Year = {$(=Max(Year))}, Month = {$(=Max(Month)-1)}, Date>} [Queue Calls])


                                      Neither expression pulls in a value though.  What do you think?

                                        • Re: Expression Needed
                                          Sunny Talwar

                                          May be if you can share a sample with your final output we might be able to help you better. What exactly are you trying to get to?

                                            • Re: Expression Needed
                                              Kathy Parker

                                              Capture.PNG

                                              Sum({<[Queue Name] = {'Customer Service Q'}, Year = {$(=Max(Year)}, Month = {$(=Max(Month))}, Date>} [Queue Calls])

                                               

                                              I am trying to creating a KPI (hence the blue dash) where I can display the sum of the Customer Service queue calls for November 2016 are displayed.  I would rather the formula contain the current year and month, not 2016 so I don't have to update every month. 

                                               

                                              @Sunny T

                                                • Re: Expression Needed
                                                  Sunny Talwar

                                                  First of all you are missing a parenthesis in your set modifier for Year

                                                   

                                                  Sum({<[Queue Name] = {'Customer Service Q'}, Year = {$(=Max(Year))}, Month = {$(=Max(Month))}, Date>} [Queue Calls])

                                                   

                                                  Does this solve your issue? May be not because The Max(Month) might still be December if you have the month of December for prior years. If that is true, you can try to restrict max month for current year or max year

                                                   

                                                  Sum({<[Queue Name] = {'Customer Service Q'}, Year = {$(=Max(Year))}, Month = {$(=Max({<Year = {$(=Max(Year))}>}Month))}, Date>} [Queue Calls])

                                                   

                                                  But the above will only work if you month is in number format. So I suggest you to disregard the above method and use dates to drive selection of a single month.

                                                   

                                                  Sum({<[Queue Name] = {'Customer Service Q'}, Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Max(Date)), 'DateFieldFormatHere'))"}>} [Queue Calls])

                                                   

                                                  The only thing you need to make sure here is that replace DateFieldFormat with the actual Date field format which would look something like this

                                                   

                                                  DD/MM/YYYY or MM/DD/YYYY or something along those lines. So, assuming you have a format = MM/DD/YYYY, this expression should work

                                                   

                                                  Sum({<[Queue Name] = {'Customer Service Q'}, Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'MM/DD/YYYY'))"}>} [Queue Calls])

                              • Re: Expression Needed
                                Sunny Talwar

                                May be this:

                                 

                                Sum({<Queue = {'Billing Q'}>} [Queue Calls])

                                • Re: Expression Needed
                                  Nitin Gaikwad

                                  Hi Kathy,

                                   

                                  Try this:

                                               Sum({<Queue names={'Billing Q'}>}[Queue Calls])

                                  if not please elaborate your query.

                                   

                                  Regards

                                  • Re: Expression Needed
                                    rgv rand

                                    Sum({<Queue Name ={'Billing Q'}>}[Queue Calls])