Visokio website     Downloads     Video tutorials     KnowledgeBase  
Bar/Line View: Daily sales compared month on month? - Visokio Forums
Bar/Line View: Daily sales compared month on month?
  • cristian January 8, 2015 7:02AM
    Hi All,
    I have a bar chart displaying all day of the month 1,..,31 on the x-axis and sales value on y-axis. I would like to split the bar chart and have two bars per day where one bar displays the current month, that is latest loaded month, and the other bar displays last/previous month sales. All other months are not of interest.

    Example of my data structure:

    Date, YearMonth, Year, Month, Day, Sales Amount

    Any suggestions on how to do this?

    Br
    Cristian
  • 7 Comments
  •     paola January 8, 2015 8:31AM
    In the DataManager, isolate the [YearMonth] field (in a new Field Organiser block, delete all other fields), de-duplicate, and add [RankMonth]=Rank([YearMonth]) field, merge then on the same field with the rest of your dataset.
    Result will be evaluation of the Month values as unique rather than multiple values, so all records that have Jan, Feb, Mar in the same year will have values 1,2,3, even if you have multiple records in each month.

    Add 2 new formula fields, that refer to this rank

    CM=
    DECLARE(
    CurrentMonth, SUBSET_MAX([RankMonth]),
    SUBSET_SUM([Sales], SUBSET2([Date],[RankMonth],[Date],CurrentMonth)))



    LM=
    DECLARE(
    CurrentMonth, SUBSET_MAX([RankMonth]),
    SUBSET_SUM([Sales], SUBSET2([Date],[RankMonth],[Date],CurrentMonth-1)))

    Split the Bar/Line view on the [Day] and use [CM] and [LM] as measures, side by side.

    See attached demo.

    image
  •     Bart January 8, 2015 8:33AM
    You can also try this solution (please see file attached)
    Attachments
    example.iok 10K
  • cristian January 8, 2015 9:45AM
    Great! Thank you. I will check both solutions.
  •     paola January 8, 2015 9:53AM
    That is a good idea Bart, thanks!
    The only issue is that you are relying on TODAY function, so at the beginning of each month the data might not be there e.g. we're in January now, but your latest sales data might be from December.

    IF(
    [YearMonth]=DATETOTEXT(TODAY,"yyyyMM"),"Current Month",
    [YearMonth]=DATETOTEXT(DATEADD(TODAY,-1,"month"),"yyyyMM"),"Previous Month",
    null)

    SubsetMax will pick up the latest month in the dataset, so might be more reliable.
  • cristian January 8, 2015 10:04AM
    Ok so what if I want the user to select a month and the view shows the selected month and previous month? Is there a max-function that applies to selections?

    Edit: A max-function like sum(Amount where max(RankMonth))
  •     paola January 8, 2015 10:10AM
    If you select month n and want to compare it to the previous one (n-1), then n month is Max value for this subset.
    You could use a variable to select the month ( [YearMonth] field to prevent 'overspill' and errors between years)
  •     Bart January 8, 2015 12:33PM
    Example 2 with header attached. Could not delete "example" file in edit mode.
    Note: formula used for [Current/Previous]=

    IF(
    [YearMonth]=DATETOTEXT([Month of report],"yyyyMM"),"Current Month",
    [YearMonth]=DATETOTEXT(DATEADD([Month of report],-1,"month"),"yyyyMM"),"Previous Month",
    null)

    [Month of report] is the variable, so month n is selected by the end user, while the values on the chart and all other views are re-calculated on the fly.
    Attachments
    example.iok 10K
    example2.iok 10K

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership