How to show the grand total of only one mesure in pivot table

[Newbie_QLIK]

Hi Qlik Community,

I hope this message finds you well.

I am seeking assistance with a pivot table configuration in Qlik Sense. I am trying to generate a pivot table similar to Figure 1, but I am consistently getting results that resemble Figure 2, and I'm unsure how to resolve this issue. I would like to know if what I am trying to achieve is feasible.

Figure 1: 

Figure 2

Here is my current setup:

Dimensions in Rows:

- Region
- Department
- Employee Name
- Dimensions in Columns:

Dimensions in Columns:

Year
Month

Measures:

- Total Orders per Month: This measure is the count of total orders.
- Average Orders per Week: This measure is calculated as [Total Orders] / (365.25 / 12 / 7).
- Average Orders per Month: This measure is calculated as [Total Orders] / (365.25 / 12).

I am having trouble achieving the desired layout and calculations as shown in Figure 1. If you have any suggestions or can provide guidance on how to configure the pivot table to get the results I need, I would greatly appreciate it.

Thank you in advance for your help!

[poklegoguy]

I don't think it is feasible with the pivot tables in Qlik Sense, if you have multiple measures, you are going to have multiple measure totals. However, you can try to hide it with CSS, please refer to some Qlik Sense CSS tutorial online to implement CSS in your app to hide it manually. 

[Kushal_Chawda · Accepted solution]

@Newbie_QLIK  There is no default setting in pivot table to do so, hence you might need to follow customised approach. You need to create customised dimension for Month & Measure so that they are aligned in a way we want.

Create a inline table in the script. It has 4 values to capture 4 measure items (Total & 3 measure)

Load * Inline [
Dim
1
2
3
4];

You need to create below Column Dimension

1) Year

2) Month : =pick(Dim,'',Month,Month,Month)

3) =pick(Dim,'Total','Total Order/Month','Avg/week','Avg/month')

Measure: Replace Measure name with actual measure expression

=pick(Dim,
rangesum([Order/Month],[Avg/month],[Avg/week]),
[Total Order],
[Avg/month],
[Avg/week])

Go to Sorting, Sorting expression for Month

=pick(Dim,
-1,
match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

[Newbie_QLIK]

Hi Poklegoguy,

Thanks for the suggestion.

Have a great day.  🙂

[Newbie_QLIK]

Hi @Kushal_Chawda,

You are the best! Thanks to your codes, I was able to generate what I was looking for.

Have a wonderful day. Your help is very appreciated!

Best,

Newbie_QLIK