Help Separating Some Data for a Line Chart

[Former Member]

Hello,

I am attempting to create a line chart that shows FY15's Cumulative Actual Spending compared to FY16's Cumulative Actual and Planned Spending. Unfortunately the data source available to me only lets me pull in one range of dates so I have to pull in all of FY15 and FY16 as one dataset. I'm having a lot of trouble figuring out a way to show the data from both years separately on the same chart.

Here's what I have so far:

In the end I need to have three lines. One for FY15 Actual, FY16 Planned, and FY16 Actual (We are currently in Period 4 of FY16). The graph I have set up currently is combining the two years so the values are roughly double what they should be, and I want to be able to view them separately in the same chart.

Any help with this would be greatly appreciated. I've searched all over the forums and I've asked several people around the office for their thoughts and I haven't made any progress.

Thanks,

Alec

[varunanand · Accepted solution]

Hi Alec,

I agree with Tammy, the best option would be to modify the BEx query for the changes to reflect in Lumira. This might take a long time with all the processes involved in a large enterprise, but proves to be rewarding in the long run.

An alternative is using the custom calculations in Lumira. Simple example, if the values showing up is exactly double then we can show only the values divide by 2. Else a little more complicated involving the Period Key.

Thank you,

Varun Anand

[Former Member · Accepted solution]

I gotta tell you guys - if SAP is trying to sell Lumira as the "fast analysis alternative to Tableau (or products like it)", then always going back to "fix it on the back end" is not going to win them a lot of fans or sales.

Alec, I'm replicating your dataset using this simplified example:

Is the below what you're trying to accomplish?

In my example, since 2015 Planned and 2015 Actual are the same values, there are only 3 lines rather than 4.  Also, because 2016 Actuals don't exist past June 2016, you get the flat line section in the purple line.

[varunanand · Accepted solution]

Hi Jay,

I agree with your comment about sending everything to the back end is not always the solution. But you should also consider the following :

- we can only give recommendations as we do not have complete visibility of the data and other factors that cause an issue.

- according to best practice majority of the data preparation should be preferably done in the backend itself to leverage an agile EDW model.

- Lumira has comparable functionalities as any other tool in the market, the approach might be different but the users would need to figure what is best for them.

Thank you,

Varun Anand

[Former Member · Accepted solution]

Yes this is exactly what I am trying to do! Would you mind guiding me through the process?

[Former Member · Accepted solution]

Based on the data I showed above, here's what I did:

In the Prepare room I:

• Created a new dimension called "Year" which substringed the "DM" column to get a year value
• Created a new dimension called "Month" which substringed the "DM" column to get the 3-character string that held the month portion.
• Converted the "Month" dimension to a number using the "Convert to Number" option available via the Month gear.  Lumira does this by duplicating "Month" and calling it "Month (2)".  "Month (2)" is now numeric.

In the Visualize room I created a line chart:

• Put Month (2) in the X-axis tray
• Put Year into the Color tray
• Put Actual and Planned as measures on the Y axis tray
• Using the gear icon next to Actual, added a Running Calculation as a Sum resetting every Year
• Do same thing for Planned measure
• Lumira will create two new measures when you do this, prefixed as "Running Sum: " - this allows you to delete the Actual and Planned measures from your view

When you set up the calculations, there is a small check box called "Stop calculation at last data point".  If you check this, it will remove the straight portion of the line that you see in the above picture.

Hope this helps.

[Former Member · Accepted solution]

Thank you so much Jay! That did the trick!

Is there any way I could filter out the running planned for 2015 without removing the running planned for 2016?

[Former Member · Accepted solution]

So I was able to do this but I lost one of the benefits of the original approach.

What I did was create a custom dimension for:

• 2015 / Actual
• 2016 / Planned
• 2016 / Actual

I did this by using this formula:

     if {Year} = "2016" then {Actual} else 0

(modify for each dimension)

I then created measures from each of those dimensions and dragged them onto a line chart.  It creates the same thing as the picture I'd posted above, but there is now no way to get rid of the horizontal section of the line for 2016 Actual.

This may not be the most efficient way to do this, but it works.  I'm stumbling around in Lumira just like you are, and I end up creating a lot of ancillary dimensions/measures to get to my destination.  🙂

[TammyPowlas]

Alec,

What is your data source?  Can you share a sample screen shot?

[Former Member]

I'm brand new to Lumira, so I might be showing you the wrong information, but I'll try to get you the right things.

Here's the variables I select at the beginning:

Here's the measures and dimensions I have selected to pull from the BW:

Lastly, here's the measures and dimensions available to me inside my lumira file. I made a few of these in the preparation tag by modifying some data (the ones labled '2'):

I hope this helps. Please let me know if you need more information!

[TammyPowlas]

A quick suggestion is to create restricted key figures for each of the years back in the BEx query - then you could show those as separate years/measures in Lumira.

[Former Member]

Unfortunately I cannot make changes to our querys. If it's possible to do this strictly through Lumira I would like to do that, but if changing the query is the best way I might be able to arrange something. (I work for a large company and this stuff is rather strictly regulated. It could take quite a while to get the changes pushed through.)

[TammyPowlas]

Alec - depending on the volume of data back in BW, it would be faster to make these types of changes in the BEx query.  I'll try to think of alternatives to this.  You could try to create calculations in Lumira based on the year?  Do you want to try that?

[Former Member]

I'd like to give the calculations a shot. Someone I talked to mentioned this possibility, but neither of us actually know how to do it. If this doesn't work I'll see about getting the changes to the query pushed through, but it could take days to weeks.

[TammyPowlas]

Please see Andreas Krause's document on Lumira calculations

You can try it from the menu: