8 Replies Latest reply on Aug 22, 2017 8:15 AM by P R

# Struggling to build line chart with 3 dimension

Mac 10.3

I am using the "date completed" field as my x axis. (month/year)

If you see the three tabs: FCR, SLA Achieved, and Happy. I would like to take those percentages and put them in a line chart. To show those % values over time (date completed). It looks like a jumbled mess each time I try do it.  The values for FCR and SLA are 0 and 1, but I only care to show the 1's, so we can hide the 0's. Also enclosed is an example of what I am looking for, that I found on google.

Thanks

• ###### 1. Re: Struggling to build line chart with 3 dimension

Hi P R,

Create calculated field [% over Time]

SUM([Number of Records])/TOTAL(SUM([Number of Records]))

Set the table calculation to compute using Date Completed or Table (across).

Then create the charts as shown in the attached workbook. Depending on what you want to show, you can design the chart in various ways. Put FCR and SLA Achieved on the filter shelf and filter out zeroes.

Hope this helps.

Ossai

• ###### 2. Re: Struggling to build line chart with 3 dimension

Hey There-

Sorry I wasn't very clear.

I need to see the % of FCR = 1 and SLA  = 1, over time. For satisfaction, I only need to see % = Happy over time.

So my three lines on the chart will be FCR, SLA and Happy. (Not Happy, sad, neutral.)

If you see the worksheets titled FCR, SLA and Happy, those show % without accounting for time. Just a snapshot of all the data.

So my combine line chart will show what those % are over time.

Does that make sense?

• ###### 3. Re: Struggling to build line chart with 3 dimension

Oh I see what you mean. I have adjusted the formula and hopefully this is what you want.

[% FCR]

ZN(SUM(IF [FCR] = 1 THEN [Number of Records] END)/TOTAL(SUM([Number of Records])))

This is fromula can be shorter but I've left it in this form to show what it is doing.

[% SLA]

ZN(SUM(IF [SLA Achieved] = 1 THEN [Number of Records] END)/TOTAL(SUM([Number of Records])))

[% Happy]

ZN(SUM(IF [Satisfaction] = 'happy' THEN [Number of Records] END)/TOTAL(SUM([Number of Records])))

The chart only shows data for 2017. Remove the additional filter if you want to display all the [Date Completed] years.

Hope this helps.

Ossai

• ###### 4. Re: Struggling to build line chart with 3 dimension

Thank you very much, one last question, I changed the default properties of those calculations to show for example 35% instead of .35, but how can I change the x axis on the chart to do the same?

• ###### 5. Re: Struggling to build line chart with 3 dimension

Right click on the axis and select 'Format...'

Then format as shown below

Hope this helps.

Ossai

• ###### 6. Re: Struggling to build line chart with 3 dimension

Thank you for all your help.

• ###### 7. Re: Struggling to build line chart with 3 dimension

You're welcome.

• ###### 8. Re: Struggling to build line chart with 3 dimension

HI Okechwuku-

Can we calculate "% happy", but exclude where "ticket ID"  is blank?