Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Just for sake of an example, i created a dummy application for you to look at. Try this:
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)
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))
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?
=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.
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
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.