2 Replies Latest reply: Mar 26, 2018 4:03 AM by Olivier Mouche RSS

    How to put into a graph multiple columns of measures (1 column = 1month)?

    Olivier Mouche

      Hello,

       

      In the case of working on a database presenting multiple columns of quantities (one column for each month), like this one:

      Produitsjanfebmarapr
      Apples sold230220160390
      Apples soon to be sold410530380640

       

       

      In order to know how many apples are sold through the year, I found out that I could calculate the evolution such as:

      january = [jan]

      february = [jan]+[feb]

      march = [jan]+[feb]+[mar]

      april =[jan]+[feb]+[mar]+[apr]

       

      However, when I try to convert these data table into a graph, here is what I get:

       

      As my months are measures and not dimensions, I can't get a graph like this:

      or even bettter:

       

      Could you help me find out a way to produce the latests graphs ?

       

      Many thanks for your help.

       

      Olivier

        • Re: How to put into a graph multiple columns of measures (1 column = 1month)?
          Petter Skjolden

          I believe this is what you are looking for:

          2018-03-24 11_40_23-_T - My new sheet _ App overview - Qlik Sense.png

           

          It can easily be achieved if you unpivot your data which is a good idea for the raw data you have if you want to create good visualization easily in a BI product like Qlik Sense:

           

          RAWDATA:
          CROSSTABLE (Month,Sales)
          LOAD * INLINE [
          Produits,jan,feb,mar,apr
          Apples sold,230,220,160,390
          Apples soon to be sold,410,530,380,640
          ];
          
          
          DATA:
          LOAD
            Produits,
            // Convert the monthnames into real dates to get them sorted correctly
            Date#(Capitalize(Month) & ' 1 2017 ','MMM D YYYY') AS Date,
            Sales
          RESIDENT
            RAWDATA;
            
          DROP TABLE RAWDATA;
          
            • Re: How to put into a graph multiple columns of measures (1 column = 1month)?
              Olivier Mouche

              Thanks Petter for that first answer, which is very useful. kindly appreciated.

               

              Now, my data are coming from 2 different documents on server.

              Therefore, I need to use the functions LOAD & concatenate LOAD to get my data.

               

              My real data are also looking like this: (but they are evolving. Therefore, I cannot write their values like in your answer)

                 

              apples sold1234
              yes230220160390
              no410530380640

               

               

              From your solution, does that mean I need to write my program as such?

               

              [RAWDATA]:
              CROSSTABLE (Month,Costs)
              LOAD [apples sold], [1],[2],[3],[4]
              FROM [lib://data/doc1.xlsx]

               

              [RAWDATA]:

              CROSSTABLE (Month,Costs)

              concatenate LOAD [apples sold], [1],[2],[3],[4]

              FROM [lib://data/doc2.xlsx]

               

              [DATA]:
              LOAD
                [apples sold],
                // Convert the monthnames into real dates to get them sorted correctly
                Date#(Capitalize(Month) & '/2018 ','M/YYYY') AS Date,
                Costs
              RESIDENT
              [RAWDATA];
               
              DROP TABLE [RAWDATA];

               

              My issue is that I got no values... in the graph...

               

               

              Many thanks for your help again.

               

              Olivier