topic Re: Help with Pivot Table in App Development

Help with Pivot Table

Nemo1 — Fri, 15 Nov 2024 20:53:52 GMT

Hello everyone

So my problem is the following.

I have a dataset, where I have a column called "Month" and another column called Sales.

I need to display the different months in QlikSense as columns. To explain myself better, I need for every month, a column. How can I do that?

thanks

Re: Help with Pivot Table

Kushal_Chawda — Tue, 06 Aug 2024 10:24:47 GMT

@Nemo1 Not sure what do you mean. Probably you can share screenshot of required format.

Re: Help with Pivot Table

Nemo1 — Thu, 08 Aug 2024 07:50:56 GMT

So I have the data in excel represented like this:

and i wanna have it in qlik sense showed like this:

thanks!

Re: Help with Pivot Table

Or — Thu, 08 Aug 2024 07:56:44 GMT

This looks like a standard pivot table, with the Measures dragged to the left (row) side and the date in the pivot (column) side. Are you having some sort of specific issue with this?

Re: Help with Pivot Table

sergio0592 — Thu, 08 Aug 2024 09:54:27 GMT

Hi,

You can try with the Generic load as

GENERIC LOAD Date, Sales, Cost RESIDENT YourTable;

Re: Help with Pivot Table

Kushal_Chawda — Thu, 08 Aug 2024 10:49:58 GMT

@Nemo1 Create pivot table with Column dimension Month. Create two measure for Sales & Cost. Drag the values of Measure to Row Dimension

Re: Help with Pivot Table

Nemo1 — Thu, 08 Aug 2024 12:34:30 GMT

It does not work, because when I do that, I get one column for the dates, and then each date represent one row. I need to have for each month, a different column.. 😞

Re: Help with Pivot Table

Nemo1 — Thu, 08 Aug 2024 12:35:18 GMT

It does not work, because when I do that, I get one column for the dates, and then each date represent one row. I need to have for each month, a different column..

Re: Help with Pivot Table

Fabiano_Martino_Intelco — Thu, 08 Aug 2024 12:44:54 GMT

Hi @Nemo1 ,

can you post a screenshot of what you get and a screenshot of ther configuration of your pivot table, so we can better understand how it is configured?

Regards

Fabiano

Re: Help with Pivot Table

Nemo1 — Tue, 13 Aug 2024 08:38:03 GMT

Hello

Sure, thanks for answering.

This is my input table:

This is what I am aiming to have

I am stuck . Any help is appreciated!

Re: Help with Pivot Table

Nemo1 — Tue, 13 Aug 2024 08:39:26 GMT

Hello,

and once I do that, how should I continue?

For more clarity, this is the table that I have:

this is what i am aiming for:

thanks in advance!

Re: Help with Pivot Table

Nemo1 — Tue, 13 Aug 2024 08:40:57 GMT

Yeah, it is not working for me.

To give a clearer idea, this is my input table:

and this is what i need:

thanks in advance 🙂

Re: Help with Pivot Table

Fabiano_Martino_Intelco — Tue, 13 Aug 2024 10:52:14 GMT

@Nemo1 ,

I created the table you need with the following code:

>>>>>>>>>>

Data:

Load * Inline [

Date, Production Cost, Rent, Manpower, Total Cost, Sales, Margin

1998, 65000, 4000, 80000, 149000, 240000, 91000

1999, 65000, 4000, 80000, 149000, 240000, 91000

2000, 62000, 4000, 80000, 146000, 267000, 121000

2001, 62000, 5000, 80000, 147000, 267000, 120000

2002, 65000, 5000, 85000, 155000, 267000, 112000

2003, 54000, 5000, 85000, 144000, 267000, 123000

2004, 54000, 5000, 90000, 149000, 267000, 118000

2005, 54000, 5000, 90000, 149000, 301000, 152000

]

;

DataUnpivot:

Crosstable(Element, Value)

Load *

Resident Data

;

DataTransposed:

Generic

Load Date, Element, Value

Resident DataUnpivot

;

Drop Table DataUnpivot;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='DataTransposed' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

Trace $(vListOfTables);

FinalTable:

Load distinct Date Resident Data;

For each vTableName in $(vListOfTables)

Left Join (FinalTable) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

Drop Table Data;

<<<<<<<<<<

Regards

Fabiano

Re: Help with Pivot Table

Fabiano_Martino_Intelco — Tue, 13 Aug 2024 12:37:59 GMT

Hi @Nemo1

sorry I made a mistake in the previous code.

This is the correct one:

>>>>>>>>>>

Data:

Load * Inline [

Date, Production Cost, Rent, Manpower, Total Cost, Sales, Margin

1998, 65000, 4000, 80000, 149000, 240000, 91000

1999, 65000, 4000, 80000, 149000, 240000, 91000

2000, 62000, 4000, 80000, 146000, 267000, 121000

2001, 62000, 5000, 80000, 147000, 267000, 120000

2002, 65000, 5000, 85000, 155000, 267000, 112000

2003, 54000, 5000, 85000, 144000, 267000, 123000

2004, 54000, 5000, 90000, 149000, 267000, 118000

2005, 54000, 5000, 90000, 149000, 301000, 152000

]

;

DataUnpivot:

Crosstable(Element, Value)

Load *

Resident Data

;

DataTransposed:

Generic

Load Element, Date, Value

Resident DataUnpivot

;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='DataTransposed' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

Trace $(vListOfTables);

FinalTable:

Load distinct Element Resident DataUnpivot;

For each vTableName in $(vListOfTables)

Left Join (FinalTable) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

Drop Table Data;

Drop Table DataUnpivot;

<<<<<<<<<<

Regards

Fabiano

Re: Help with Pivot Table

Nemo1 — Mon, 26 Aug 2024 09:30:36 GMT

thanks so much for your answer and sorry for taking so long to get back to you.

the problem is, the data I gave in the table below is just a sample, the reality is, that I have a huge dataset, and it would take me ages to type the data from this dataset once again.

there is no way to make this process more direct? without the need to type every number in the INLINE??

thanks

Re: Help with Pivot Table

Fabiano_Martino_Intelco — Wed, 28 Aug 2024 07:07:45 GMT

Hi @Nemo1

I used the INLINE statement in my code to give you a self-contained example.

If you have your dataset in another format (Excel file, database tables, ...) you should modify the code in order to load your dataset from your specific source.

Regards

Fabiano

Re: Help with Pivot Table

Nemo1 — Thu, 05 Sep 2024 09:17:47 GMT

thank u so much!!