Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

olivier_mouche
New Contributor

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

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

2 Replies
MVP
MVP

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

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;

olivier_mouche
New Contributor

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

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

Community Browser