Expression Needed

[Anonymous]

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?

[sunny_talwar]

Previous year should be like this

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

[Anonymous]

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?

[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?

[Anonymous]

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

[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])