How can we help?

New post
0

Bar chart that only pulls last column of data

Avatar
Joey Brannon
None
Follow

We have a spreadsheet that lists salespeople in A:A with monthly sales in columns to the right. Each month a new column is added. Salespeople come and go so the list gets longer as new salespeople are added. The X axis of the bar chart needs to be the list of salespeople and the series needs to be the last column (most recent month). I know this is a simple problem to solve but I can't figure it out.

Date Votes

1 comment

  • Avatar
    Nick Payne Official comment

    Hi Joey,

    Thanks for making a post on our community board.

    If you wish to consistently pull the last column present in your data, you’ll need to do a lookup that will find the name of last column and then create a datasource reference to pull that column of data. The example I have included assumes your data is in the following format:

    First you need to grab the last column name with a function like this:

    LOOKUP(LAST(SELECT(@Row1_datafile,@Row1_datafile!=BLANK())),@Row1_datafile,@Row1_mapping_file)

    where @Row1_datafile is the row which contains all the dates and @Row1_mapping_file is the row from the separate datasource mapping file.

    This function calls a mapping file (comprised of a single row where each column contains the column names A, B, C,… etc) to provide the name of the last column with data. For the example above, the function will return "F".

    From there you can feed this LOOKUP function into a DATASOURCE function that will pull the column of data that you need.

    DATASOURCE(“datasource ID”,CONCAT(
    LOOKUP(LAST(SELECT(@Row1_datafile,@Row1_datafile!=BLANK())),@Row1_datafile,@Row1_mapping_file),
    “:”,
    LOOKUP(LAST(SELECT(@Row1_datafile,@Row1_datafile!=BLANK())),@Row1_datafile,@Row1_mapping_file)))

    The datasource ID can be found in the “About this DataSource” section when you view the relevant datasource.

    The mapping file can be generated within any spreadsheet program but if you prefer that we send you a file to use, please send an email to support@klipfolio.com.

    I hope this was helpful. If you need some help building a specific Klip with the function I included or have any questions, please do not hesitate to contact us via a support ticket or by emailing support@klipfolio.com.

    Best,

    Nick

Please sign in to leave a comment.