Pivot Table

[JMAROUF]

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:

[vinieme12]

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)

[JMAROUF]

Thank you @vinieme12 ,

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

[vinieme12]

then just manipulate the column label !

Row Dimension = Customer

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

Measure = Sum(Amount)

[JMAROUF]

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

Eg:

[vchuprina]

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

[JMAROUF]

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.

[vinieme12]

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
)

[vinieme12 · Accepted solution]

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