12 Replies Latest reply on Dec 3, 2018 6:32 AM by Renea Rice

# 3 Month Trend with KPI

How do I create a line chart showing only 3 months at a time? On the filter, I need April - June, May - July, June - August, etc. Is there a specific way to structure the data to show this?

• ###### 1. Re: 3 Month Trend with KPI

Hi Renea,

Are you expecting something like this?

• ###### 2. Re: 3 Month Trend with KPI

Yes and no.

If that can be one continuous line, then yes.

In a filter, I would like to be able to click April – June or May – July or June – August, etc.

I have tried using a parameter with a calculation and couldn’t get it to work.

I have tried this calculation but this is what it looks like to the visual:

IF (MONTH () = 4)or MONTH() = 5 or MONTH() = 6 THEN 'April - June'

ELSEIF (MONTH() = 5) or MONTH() = 6 or MONTH() =7 THEN 'May - July'

ELSEIF (MONTH() = 6) or MONTH()= 7 or MONTH()=8 THEN 'June - August'

ELSEIF (MONTH() =7 or MONTH()= 8 or MONTH()=9) THEN 'July - September'

ELSEIF (MONTH() =8 or MONTH()= 9 or MONTH()=10) THEN 'August - October'

ELSEIF (MONTH() =9 or MONTH()= 10 or MONTH()=11) THEN 'September - November'

ELSEIF (MONTH() =10 or MONTH()= 11 or MONTH()=12) THEN 'October - December'

ELSEIF (MONTH() =11 or MONTH()= 12 or MONTH()=1) THEN 'November - January'

ELSEIF (MONTH() =12 or MONTH()= 1 or MONTH()=2) THEN 'December - February'

ELSEIF (MONTH() =1 or MONTH()= 2 or MONTH()=3 ) THEN 'January - March'

END

Renea Rice

APW Analyst/Plant Performance

Nissan Powertrain Manufacturing

Email: Renea.Rice@nissan-usa.com<mailto:Renea.Rice@nissan-usa.com>

Phone: 931-962-5812

• ###### 3. Re: 3 Month Trend with KPI

Hi Renea,

I have tried something similar to your approach.

Let me know this helps!

• ###### 4. Re: 3 Month Trend with KPI

Yes I like the line.

Can be there a filter to choose which months you want to look at?

Renea Rice

APW Analyst/Plant Performance

Nissan Powertrain Manufacturing

Email: Renea.Rice@nissan-usa.com<mailto:Renea.Rice@nissan-usa.com>

Phone: 931-962-5812

• ###### 5. Re: 3 Month Trend with KPI

Hi Renea,

Please see the attached workbook if this is what you are looking for..

I have created a Month Parameter that gives the options to choose all or any 3 consecutive months and a 3 month Filter calculation to make the sheet function according to the drop down selection.

• ###### 6. Re: 3 Month Trend with KPI

Yes Renea,

Ofcourse. Use the Months column as filter. We can also provide multiple selection to user.

Hope this helps. Plz mark this as helpful and correct if this resolves your question.

Anything more let me know!

1 of 1 people found this helpful
• ###### 7. Re: 3 Month Trend with KPI

Yes! Can you walk me through how you created the parameter and filter?

Renea Rice

APW Analyst/Plant Performance

Nissan Powertrain Manufacturing

Email: Renea.Rice@nissan-usa.com<mailto:Renea.Rice@nissan-usa.com>

Phone: 931-962-5812

• ###### 8. Re: 3 Month Trend with KPI

Hi Renea,

I am glad to walk you through the steps.

Assuming the MONTH field in my workbook is similar to the MONTH field in your data, right click on it and select create parameter

In addition to the 12 months in the parameter list, add a new value 0..

Drag the 0 value to the top of the list and change the display names as following

Now create the calculated field, '3 month Filter' using the code below

Now place the MONTH field on the rows and your measure (here, I am using Sales) on the columns.

Drag '3 month Filter' to Filters and set the filter to 1.

Finally, right click on the Month Parameter an select Show Parameter Control, that give you a filter drop down with the month selections

And..there you have the view you need.

Let me know, if you have any questions

• ###### 9. Re: 3 Month Trend with KPI

Sushmitha –

Thank you for the walk through. I got all the steps and tried the parameter to select which months I wanted to look at and it didn’t change the view to the selected months.

What could I have done wrong?

Thanks!

Renea Rice

APW Analyst/Plant Performance

Nissan Powertrain Manufacturing

Email: Renea.Rice@nissan-usa.com<mailto:Renea.Rice@nissan-usa.com>

Phone: 931-962-5812

• ###### 10. Re: 3 Month Trend with KPI

Hi Renea,

Could you please attach your workbook (packaged). If it isn't possible, could you share a screenshot of your sheet showing the dimensions and measures list (like the one I shown below) ?

• ###### 11. Re: 3 Month Trend with KPI

Sushmitha –

I believe I got it figured out. This is what I have below.

Thank you!

Renea Rice

APW Analyst/Plant Performance

Nissan Powertrain Manufacturing

Email: Renea.Rice@nissan-usa.com<mailto:Renea.Rice@nissan-usa.com>

Phone: 931-962-5812

• ###### 12. Re: 3 Month Trend with KPI

How do I get the line to be a gradient like in the picture below?

I have a target line. If the actual is over the target then the color to move to red. If below target then color to move to green.