Qlik SaaS - Qlik Sense - Create Report like Financial Statement (Pivot Table)

[profilejamesbond]

Hi,

I would like to create report like financial statement with some layout variation.
I want to achieve layout like attached image. How to create this layout in the Qlik Sense? I tried with Pivot Table and also with P&L Table and achieved left hand side layout with create dimension of Total Sales Current Month, previous month, and differences but when I added the expressions then each expression appeared under the MonthYear

Thanks

[Nagaraju_KCS]

That layout is typical for financial reports and is best achieved using the Straight Table object in Qlik Sense, where you define each column (current month, previous month, difference) as a distinct measure.

The problem you encountered with the Pivot Table (where the expressions appeared nested under the MonthYear column) occurred because you tried to place the time periods (current month, previous month) in the dimension and the repeated measures under that dimension. For this specific layout, you should use fixed columns for measures

[profilejamesbond]

Hi @Nagaraju_KCS;

Can you share me the example or work around with Straight Table ?

With Straight Table I don't get the result what you mentioned.

[profilejamesbond · Accepted solution]

I made solution like this and it works:
1. 

load * inline [

MeasureType

Total Sales Current Month
Total Sales Previous Month
Differences

];

and Added MeasureType as a dimension in the Pivot Table.

2.

In the Pivot Table, I added only one Expression with:

Pick(Match(MeasureType, 'Total Sales Current Month', 'Total Sales Previous Month', 'Differences'), Num(Expression 1 or Master Item 1), '#,##0'), Num(Expression 2 or Master Item 2), '#,##0'), Num(Expression 3 or Master Item 3), '#.##0%'))

For the total: 

If(Dimensionality()=0, // <-- TOTAL ROW 

Num(Total Sales Current Month, '#.##0,00€;-#.##0,00€'), // show only Sales Current in total

Pick(Match(MeasureType, 'Total Sales Current Month', 'Total Sales Previous Month', 'Differences'), Num(Expression 1 or Master Item 1), '#.##0,00€;-#.##0,00€'), Num(Expression 2 or Master Item 2), '#.##0,00€;-#.##0,00€'), Num(Expression 3 or Master Item 3), '#.##0%')))

Dimensionality() = 0 → full pivot grand total (no dimensions left)

Dimensionality() = 1 → total at the level where only MeasureType remains

Dimensionality() > 1 → normal row values

Adjust expressions according to your requirements