16 Replies Latest reply: Apr 25, 2016 6:26 PM by Cassadi Myers

# Aggr for distinct % across Quarters

I am trying to show percentage of Total by Quarter. I am not able to get the correct Quarter Percentage unless I have the quarter selected. I have seen some examples in the community but have seen one using set analysis.

Here is my count formula.

I am counting specific ticket types.

=count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket)

I am trying to divide that by the quarter count.

sum(Aggr(NODISTINCT Count(Total{<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket), [Fiscal Year-Quarter]))

I am asking for help on the denominator. So that its not taking the total of all the tickets. I only want to divide by the tickets count of the quarter.

Thank you,

• ###### Re: Aggr for distinct % across Quarters

May be try this:

Sum(TOTAL Aggr(NODISTINCT Count(Total{<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket), [Fiscal Year-Quarter]))

or

Sum(TOTAL<[Fiscal Year-Quarter]> Aggr(NODISTINCT Count(Total{<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket), [Fiscal Year-Quarter]))

• ###### Re: Aggr for distinct % across Quarters

Could you give some more information about the context you are using your expression in? Are you using it in a chart? What are the dimensions and expressions you are using?

And could you also give some numbers, what you expect to get from the numerator and denominator?

Or could you upload a small sample QVW with a description of your expected result?

• ###### Re: Aggr for distinct % across Quarters

I have many data sources in this dashboard. The context for this chart is looking at the closed tickets. I have a contexts that link into a master calendar. I am also looking at only a subset of the tickets. I have created some logic that is called FCR "First call resolution". Then I am looking at only employees in a certain cost center.

The dimensions I am using are Ticket channel and Fiscal quarter. My goal is to see the percentage of workload to a specific channel in a specific quarter.

My problem is that the % by channel is taking the total tickets for all years. I want to get just the total by quarter. My data is sensitive and I cant post it as an example.

• ###### Re: Aggr for distinct % across Quarters

Since you are using Fiscal Quarter as dimension, you might want to try this using Total qualifier with the field name:

=Count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket) / Count(TOTAL <[Fiscal Quarter]> {<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket)

• ###### Re: Aggr for distinct % across Quarters

Still not working. When the table has no filter the last column is capturing the whole ticket count. When I select a quarter I get the number I want. I believe I need to use AGGR I am just not sure where to put it.

• ###### Re: Aggr for distinct % across Quarters

Your fiscal quarter columns looks like a calculcated dimension.

Could you please post the expression you are using for this dimension?

• ###### Re: Aggr for distinct % across Quarters

=IF(Cost_Center_Join='US0****'and [Fiscal Year-Quarter]>='2015-1',[Fiscal Year-Quarter],NULL())

• ###### Re: Aggr for distinct % across Quarters

I assume you have used that exact same field name also in the TOTAL qualifier field list:

=Count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket) /

Count( TOTAL<[Fiscal Year-Quarter]> {<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0***'}>} Distinct Ticket)

• ###### Re: Aggr for distinct % across Quarters

Just for sake of an example, i created a dummy application for you to look at. Try this:

• ###### Re: Aggr for distinct % across Quarters

See this example:

• ###### Re: Aggr for distinct % across Quarters

Thank you for all the help. I am almost there.

I can now get a total by quarter. But when I try to divide to get a percentage it wont work but if I divide the two columns I get the correct %. My goal is to get the % into a stacked bar chart.

Dimensions

=IF(FCR='Yes' and CC_CODE='US0***',[Fiscal Year-Quarter],NULL())

Channel

Expressions

Tickets Closed : =count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0****'}>} Distinct Ticket)

Total Tickets by Quarter :

=Count(TOTAL <[Fiscal Year-Quarter], CC_CODE,FCR,CONTEXT>distinct Ticket)

Try to divide to get % (Not Working)

=count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0******'}>} Distinct Ticket)/

(Count(TOTAL <[Fiscal Year-Quarter], CC_CODE,FCR,CONTEXT>distinct Ticket))

If I just divide the columns I get the right %

Column(1)/Column(2)

• ###### Re: Aggr for distinct % across Quarters

Are you sure not getting any result, or is the result not what you expect? May be you need the same set analysis in the denominater as numerator?

=Count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0******'}>} Distinct Ticket)/

(Count(TOTAL <[Fiscal Year-Quarter], CC_CODE,FCR,CONTEXT>distinct {<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US0******'}>} Ticket))

• ###### Re: Aggr for distinct % across Quarters

Strange...

a) I don't think there is a need to list fields that are not used as dimensions in your TOTAL field list, so given

, CC_CODE,FCR,CONTEXT are not part of your dimensions, this should return the same value:

=Count(TOTAL <[Fiscal Year-Quarter]>distinct Ticket)

b) Have you enabled any other option, like the 'relative' option for your third expression?

• ###### Re: Aggr for distinct % across Quarters

=count({<CONTEXT={'Ticket_Closed'},FCR={'Yes'},CC_CODE={'US****'}>} Distinct Ticket)/

(Count(TOTAL <[Fiscal Year-Quarter]>distinct Ticket))

When I took off the Relative option the calculation works.

Thank you all so much for the help.

• ###### Re: Aggr for distinct % across Quarters

Life would have been so much easier had you tried pulling a small sample together to post on the community. Its always a good idea to post a sample to get quick help. Had it not been for Stefan, we would all be guessing what's wrong.

Anyways have a wonderful rest of the day.

Best,

Sunny

• ###### Re: Aggr for distinct % across Quarters

Sorry for the hassle. I am on a super time crunch and the report I am working with has tons of data sources and data.

I really appreciate all the help and I will do better with my examples next time.