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

# Expression Needed

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

Try:

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

- Marcus

• ###### Re: Expression Needed

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

• ###### Re: Expression Needed

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

• ###### Re: Expression Needed

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

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

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

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

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

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

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

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

May be this:

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

• ###### Re: Expression Needed

Hi Kathy,

Try this:

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