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

3 Month Trend with KPI

Renea Rice Nov 27, 2018 8:35 AM

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

Akram Ebrahim Nov 27, 2018 9:36 AM (in response to Renea Rice)

Hi Renea,

Are you expecting something like this?
Like Show 0 Likes(0)

Actions
2. Re: 3 Month Trend with KPI

Renea Rice Nov 27, 2018 9:45 AM (in response to Akram Ebrahim)
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

image002.jpg 9.8 KB
Like Show 0 Likes(0)

Actions
3. Re: 3 Month Trend with KPI

Akram Ebrahim Nov 27, 2018 10:57 AM (in response to Renea Rice)
Hi Renea,

I have tried something similar to your approach.

Let me know this helps!

Continuous 3 Months.twbx 416.8 KB
Like Show 0 Likes(0)

Actions
4. Re: 3 Month Trend with KPI

Renea Rice Nov 27, 2018 11:12 AM (in response to Akram Ebrahim)

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
Like Show 0 Likes(0)

Actions
5. Re: 3 Month Trend with KPI

Sushmitha Reddy Nov 27, 2018 12:27 PM (in response to Renea Rice)
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.

3 month trend.twbx 1.1 MB
Like Show 0 Likes(0)

Actions
6. Re: 3 Month Trend with KPI

Akram Ebrahim Nov 28, 2018 2:00 AM (in response to Renea Rice)

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
Like Show 0 Likes(0)

Actions
7. Re: 3 Month Trend with KPI

Renea Rice Nov 28, 2018 4:30 AM (in response to Sushmitha Reddy)

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
Like Show 0 Likes(0)

Actions
8. Re: 3 Month Trend with KPI

Sushmitha Reddy Nov 28, 2018 3:56 PM (in response to Renea Rice)

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
Like Show 0 Likes(0)

Actions
9. Re: 3 Month Trend with KPI

Renea Rice Nov 29, 2018 5:03 AM (in response to Sushmitha Reddy)

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
Like Show 0 Likes(0)

Actions
10. Re: 3 Month Trend with KPI

Sushmitha Reddy Nov 29, 2018 8:19 AM (in response to Renea Rice)

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) ?
Like Show 0 Likes(0)

Actions
11. Re: 3 Month Trend with KPI

Renea Rice Dec 3, 2018 5:00 AM (in response to Sushmitha Reddy)
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

image002.jpg 29.9 KB
Like Show 0 Likes(0)

Actions
12. Re: 3 Month Trend with KPI

Renea Rice Dec 3, 2018 6:32 AM (in response to Sushmitha Reddy)

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.
Like Show 0 Likes(0)

Actions