topic Re: Pivot Table in App Development

Pivot Table

JMAROUF — Wed, 27 Apr 2022 10:25:58 GMT

Hi Everyone,

I can't display the desired output in qlik, bellow the result I have and the one I want to achieve, can anyone help please?

Data

transaction:
Load * inline [
Date,Amount,Customer
01/01/2010,11,1
01/02/2010,12,2
01/03/2010,13,3
01/01/2020,21,1
01/02/2020,22,3
01/03/2020,23,2
];

[Calendar]:
Load * inline [
Date,Year,MonthN
01/01/2010,2010,1
01/02/2010,2010,2
01/03/2010,2010,3
01/01/2020,2020,1
01/02/2020,2020,2
01/03/2020,2020,3
];

Desired Result:

Current Result:

Re: Pivot Table

vinieme12 — Wed, 27 Apr 2022 11:00:09 GMT

Looks like you are adding Two MEasures ; you don't need to add two measure as the data will aggregate based on the dimensions used, same like in an excel pivot table

Just Use Row Dimension = Customer

Column Dimension= YEar

Measure = sum(Amount)

Re: Pivot Table

JMAROUF — Wed, 27 Apr 2022 12:30:25 GMT

Thank you @vinieme12 ,

I'm looking to diplay two columns, the business wants to show a label above the year like that:

Re: Pivot Table

vinieme12 — Wed, 27 Apr 2022 12:50:14 GMT

then just manipulate the column label !

Row Dimension = Customer

Column Dimension = Date=Year&chr(10)&'Amount ' &YearNum

Measure = Sum(Amount)

Re: Pivot Table

JMAROUF — Wed, 27 Apr 2022 13:09:22 GMT

Thanks, that sounds good, but the business will add other measures, like for example count of transactions, than this solution will not work:

Eg:

Re: Pivot Table

vchuprina — Wed, 27 Apr 2022 13:16:20 GMT

Hi,

You should calculate 2020-2010 in the script and then you can show in pivot table

transaction:
LOAD
     Date,
     NUM(Amount) AS Amount,
     Customer,
     Year(Date) AS Year;
Load * inline [
Date,Amount,Customer
01/01/2010,11,1
01/02/2010,12,2
01/03/2010,13,3
01/01/2020,21,1
01/02/2020,22,3
01/03/2020,23,2
];

TotalTMP:
LOAD
     Amount AS Amount1,
     Customer
Resident transaction
Where Year = '2020';

Left Join(TotalTMP)
LOAD
     Amount AS Amount2,
     Customer
Resident transaction
Where Year = '2010';

Concatenate(transaction)
Total:
LOAD
     Amount1 - Amount2 AS Amount,
     Customer,
     '2020-2010' AS Year
Resident TotalTMP;

DROP Table TotalTMP;

Result:

But this is a bit difficult to maintain, so I guess you can use a Straight table with the customer as Dimension and the following expressions:

2010: SUM({<Year={'2010'}>}Amount)

2020: SUM({<Year={'2020'}>}Amount)

2020-2010: SUM({<Year={'2020'}>}Amount) - SUM({<Year={'2010'}>}Amount)

Result:

In my example, everything is hardcoded, but you can use functions in Set analysis and your chart will be dynamical

Regards,

Vitalii

Re: Pivot Table

JMAROUF — Wed, 27 Apr 2022 14:14:22 GMT

Thanks @vchuprina ,

Your solution works well in a simple data model, the problem is we are in a complicated dataset with multiple facts concatenated using generic keys to avoid circular loops, and we should recalculate the measure and add it to the appropriate fact for each measure added to the pivot.

Re: Pivot Table

vinieme12 — Wed, 27 Apr 2022 14:21:00 GMT

IMHO you should push back on such silly cosmetic requests as this unnecessarily makes a simple thing tedious , but anyways here's the solutions

transaction:
Load * inline [
Date,Amount,Customer
01/01/2010,11,1
01/02/2010,12,2
01/03/2010,13,3
01/01/2020,21,1
01/02/2020,22,3
01/03/2020,23,2
];

[Calendar]:
Load * inline [
Date,Year,MonthN
01/01/2010,2010,1
01/02/2010,2010,2
01/03/2010,2010,3
01/01/2020,2020,1
01/02/2020,2020,2
01/03/2020,2020,3
];

//New table for custom headers
TableHeaders:
Load * Inline [
hYear,hKpi,hKpiSrNo
2010,Amount_2010,1
2010,Count_2010,2
2020,Amount_2020,3
2020,Count_2020,4
];

Row Dimension = Customer

Column Dimension = hYear ,hKpi

Measure =

pick(hKpiSrNo
, sum({<Year={'2010'}>}Amount) //hKpiSrNo=1
, count({<Year={'2010'}>}Amount) //hKpiSrNo=2
, sum({<Year={'2020'}>}Amount) //hKpiSrNo=3
, count({<Year={'2020'}>}Amount) //hKpiSrNo=4
)

Re: Pivot Table

vinieme12 — Thu, 28 Apr 2022 05:29:17 GMT

@JMAROUF kindly close the thread by marking response as a solution if it worked for you