Best Way to Represent Dates as Columns in Table

First time I've come across a requirement like this. The goal is to see each month and have a calculation of the variance %increase or %decrease for that month and highlight them when it's a certain amount. The table view isn't necessarilly a requirement but the series contains over 200 records so i'm finding it hard to represent that in anything but a table, but even with the table i can't figure out the best way to get the dates across the top by month. Another problem this introduces is color rules. If I want to highlight any value over say +- 50% I can't because you choose a field, and choose color logic. I would have to have as many color rules as I do months of data (since each date might be its own column grouped monthly).

 

I tried experimenting with uncollapse columns but I don't want to have to define dates until the end of time as the value for a new column. Thoughts? See the attached small excerpt to see how it looks in Excel. Again, the "Account 1,2 3" is currently over 200 unique ones

 

Table.PNG

Comments

  • Have you tried using a Sumo card?  That is really the best way to pivot data in a visualization right now.

     

    I ran into this issue in the opposite direction.  I had a data set with the months listed as separate columns and I needed a flat file.

     

    I was able to find a dynamic SQL statement to collapse the columns regardless of how many dates there were.  Dynamic SQL is outside of my comfort zone, but I will share the code with you here to see if you can use it to help you "pivot" or uncollapse your data.

     

    SELECT
    GROUP_CONCAT(CONCAT('Select `Product Type`,',
    QUOTE(COLUMN_NAME),
    ' AS `Date`, ',
    '`',
    COLUMN_NAME,
    '` as `Revenue`',
    'FROM original')
    SEPARATOR ' UNION ALL ') AS product_info
    FROM
    INFORMATION_SCHEMA.COLUMNS
    WHERE
    TABLE_NAME = 'original'
    AND COLUMN_NAME NOT IN ('Product Type');

    CREATE PROCEDURE transpose()

    BEGIN

    SELECT product_info into @sql2 FROM for_transpose;

    SET @str=concat('create table products as ',@sql2);

    PREPARE q from @str;
    EXECUTE q;

    END

     

    call transpose;

     


    SELECT
    `Product Type`,
    STR_TO_DATE(REPLACE(`Date`, '/', ' '), '%m %d %Y') AS `Date`,
    ROUND(`Revenue`, 2) AS `Revenue`
    FROM
    products
    ORDER BY `Product Type` , `Date`

     

     

     

    These steps essentially do the opposite of what you are looking for, but perhaps they will help you get to your answer.

     

    Good luck

  • @guitarhero23,

     

    Did the reply from @ST_-Superman-_ help? If so please mark it solved by clicking on the "Accept as Solution" button.

     

    Thanks!

This discussion has been closed.