Aggr for distinct % across Quarters

[Anonymous]

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,

Cassadi

[sunny_talwar]

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

[Anonymous]

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)

[sunny_talwar]

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

[swuehl · Accepted solution]

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?

[Anonymous]

=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.

[sunny_talwar]

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

[Anonymous]

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.