0

Bar/Line Chart using Daterange with Lookup function for series

Hi,

I have a Bar/Line Chart showing 'sales' information from 2016. For the series I am using Chart type Lines and a Lookup function Lookup(daterange, A:A, B:B).

Daterange has: 2160101,2160102, 2160103... 20161231.

How can I make the Line chart show a cumulative value instead of a day by day value? 

8 comments

  • Avatar
    Josh Cohen-Collier Official comment

    Hi Peter,

    Thanks for using our community!

    If what you are looking to do is just get the cumulative total at each day(eg Dec 31 would be Dec 31 + Dec 30+Dec 29), then you can wrap your series formula in a CUMULATIVE function.

    If you are looking to instead aggregate data by the month, you can use the GROUPBY function to group your series by the month, where you convert to yyyyMM from yyyyMMdd using the DATE_CONVERT function.
    You would need to make sure you convert your X-Axis as well, and GROUP it.

    Hope this helps!

    Best regards,
    Joshua

  • 0
    Avatar
    Peter Vermeulen

    Hello Josh Cohen-Collier,

    Thank you for the answer, adding 'Cumulative' to the results in the lookup does it.

     

    Thanks you

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Peter,

    No problem!
    Happy to help.

    Best regards,
    Joshua

  • 0
    Avatar
    Wink Wink

    Hello, I hope you can help me. I´m a Klipfolio newbee and I´m trying to built a very simple Klip but I´ve been reading through guides and tutorials for the last 2 days and I just can´t figure it out. 

    What I want is to build a simple line chart to show a Visits Trend by day and be able to filter by date. So, I already created the date filter using a User Control Input element and I have built the line chart but I can´t link the date range filter with the line chart....I believe it has to do with LOOKUP but I have no idea how to use the function and where to place it in this particular case. 

    Could you give me a hand please?

    Thank you!

    Stella

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Stella,

    Absolutely we can help!
    What I think would be best for your situation would be to submit a support ticket to support@klipfolio.com, which an explanation of your problem, the name of the Klip you are working in, and making sure support access is enabled.

    Then, a dedicated Klipfolio support agent can jump in and take a look manually at what could be the problem, and what we can do to help fix it.

    Thanks for posting in our community!

    Best regards,
    Joshua

  • 0
    Avatar
    Manu Goyal

    Hi Joshua,

    I need to create a bar chart total lead falls into certains data ranges..

    I am fetching data from podio in CSV format.For that i am using mapflat function to fetch complete data.
    I have two app in podio marketing and lead app Now i need to plot total lead from lead app which falls into data ranges series into marketing app.How to do it. (Start and END date is given )in marketing app.Bar chart need to be plotted.

  • 0
    Avatar
    Janice Janczyn

    Hi Manu,

    It looks like you want to count the number of leads per week. If so, your bar/line chart would have to look something like

         x-axis (end of week): @D:D   (the Date - end column in the datasource on the right)

         series: LOOKUP( &x-axis,
                                      GROUP@( A:A ),
                                      COUNTDISTINCT( @A:A ) )

         where @A:A is the Created on column in the datasource on the left.

    If this isn't the scenario you are describing, please email support@klipfolio.com for assistance.

     

    For the LOOKUP to work properly, ensure the dates returned by &x-axis and @A:A are the same format. For details please refer to the LOOKUP function in the Klipfolio Functions article. 

    Thanks,
             Janice

     

     

  • 0
    Avatar
    Rabia Chhabra

    Hi Team,

    I am trying to achieve a dynamic date on the Bar-graph(X-axis) as highlighted below.
    Could you please suggest if that is feasible?

    Thanks!

Please sign in to leave a comment.