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
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?
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]))
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?
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.
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)
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.
Your fiscal quarter columns looks like a calculcated dimension.
Could you please post the expression you are using for this dimension?
=IF(Cost_Center_Join='US0****'and [Fiscal Year-Quarter]>='2015-1',[Fiscal Year-Quarter],NULL())
See this example:
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)