Skip to Content
author's profile photo Former Member
Former Member

Dynamic X axis in line chart

Hi All,

I'm making a line chart in Webi in which different lines represent different products. On the Y axis I have a measure (no problems here) and on the X axis I have months. Naturally, different products enter the chart at different points along the X axis because they didn't all enter the portfolio at the same time as you can see in the print screen. My problem is though, that I want the start point for all of the lines in the chart to be at the X-Y intersect, not the month that they actually started. The values for the X axis should then be 1 - 24 or something similar to cover a 2 year period. Newer products would then have a line that stopped at some point in the middle of the chart, rather than one that started in the middle.

I've been trying to create a new variable where min([Month]) is equal to 1 and then each month after that would be +1, but I'm not having any luck.

Thanks in advance for any suggestions.

pastedImage_2.png (11.9 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 28, 2014 at 05:02 AM

    Hi,

    Since Different Products enter at different points, there will be no value for those products in that particular month,

    Eg:

    See the above Example for Product 2 there is no value for 201301 month, so when u convert to chart it starts from 201302.

    But if u insert a variable =if(isnull([Y - Axis];0;[Y-Axis])

    Then 0 will be inserted in place of null values,

    So when u convert this into a chart all the products start and end at the same point but rises and falls based on the values.

    Regards

    Subasree


    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Subasree,

      Thanks for posting, though your solution isn't quite what I'm looking for.

      It's primarily comparing the slopes of the lines that is of interest. So I want to be able to have all lines start with their first non-null data point at the Y-axis, regardless of which month the data is associated with. I'm thinking that I will need some sort of count variable which counts the number of months with data for each product. Then the first month for each product will equal 1 in the count variable, the second month will equal 2, third month will equal 3 etc. The count variable would then be placed on the X-axis instead of [Month] therefore having all products start at the y-axis, and end at some point in the middle of the chart when the line exhausts the number of data points.

      If I use =Count([Month]) foreach([Product]) , it returns the number of months for each product as I was hoping, but I can't get the result to be a string to be able to put it on the X-axis.

      This is what I've been trying to accomplish but haven't succeeded yet. I'm not even sure if it will work even if I do succeed in getting the count variable as a string, but I'm not sure how else to approach it. I hope that makes sense.

      Regards

      Darrel

      Update:

      OK, I've managed to get it to a string by using two variables. The first is : *Month = Runningsum(count([Month];([Product])) which gives me a number 1 - x for each month for each product. The second is tonumber([*Month];"0") which converts the *Month variable to a string and is saved as a dimension. But, when I place it on the X-axis on the chart, it returns #MULTIVALUE, and all of the lines are flat.

  • Posted on May 29, 2014 at 01:04 PM

    Hi Darrel,

    Try using the DaysBetween and Min functions and the "In" operator. This will find the number of days between the "Order Dt" and first "Order Dt" for a "Product".

    Example:

    =DaysBetween( Min([Order Dt]) In ([Product]);[Order Dt])

    Best of Luck,

    Jeremy

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Update again.

      Very close now. I succeeded in creating a table like table 3 above by using two variables. The first is *Month = RunningSum(Count([Month];[Product])) which I threw an if statement around so that values less than 10 came back as 01, 02 instead of 1, 2 etc. Then the second variable was *Month2 = [*Month] in([Product];[Model]). I put *Month2 in my table and was then able to take away the original Month variable and keep all my data points. So, I threw this onto the X-axis of my chart:

      The lines all start at the Y-axis! The values for the X-axis are all #Multivalue though, but that's not really a problem because like I said earlier, it's comparing the slopes that is of interest. The problem is that the lines continue on flat (at the value of the latest data point) when all of the months for that product have run out.

      I've been stuck at this point for a while now :/ Anyone out there have any ideas?

      Thanks in advance.

      Darrel

      upload.jpg (152.1 kB)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.