Sum and Count at the bottom of the same table.

[rahulsud007]

Dear all

I have requirement in which I need count of items and sum of items in the bottom row as mentioned in the image in a straight or pivot table.

But in Single object not in multiple objects.

Please suggest me.

[qlikviewwizard]

Hi Rahul,

Are u  able to manage to get the solution?

[rahulsud007]

No worries, you have been very helpful.

[rahulsud007]

No I am working on it.

[sunny_talwar]

I am glad I proved somewhat helpful.

Best,

Sunny

[jagan · Accepted solution]

Hi Rahul,

Please find attached file for solution.

Regards,

Jagan.

[sunny_talwar]

Jagan can you share some screenshots of what you have done as I am working with PE and would love to know how it can be done.

Thanks,

Sunny

[rahulsud007]

Hi Jagan, the above format is correct small changes in expression will make it perfect

=If( [Item No.] = 'Percentage',

Num(Sum(TOTAL [0-1M])/Sum(TOTAL TotalInventory), '#,##0.000'),

If( [Item No.] = 'Count',

Num(Count(TOTAL [0-1M]), '#,##0.000'),

if([Item No.] = 'Sum', Num(Sum(TOTAL [0-1M]), '#,##0.000'),

Num(Sum([0-1M]), '#,##0.000'))))

[sunny_talwar]

[jagan]

Hi Sunny,

Script changes:

Table:

LOAD [Item No.],

     Description,

     [0-1M],

     [2-3M],

     [4-6M],

     [7-12M],

     [13-24M],

     [25 - 36M],

     [>36M],

     TotalInventory

FROM

Requirement.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

ADD ONLY LOAD

*

INLINE [

Item No.

'Sum'

'Count'

'Percentage'];

Chart level changes:

Disabled all totals

Expression:

=If( [Item No.] = 'Sum',

Num(Sum(TOTAL [0-1M]), '#,##0.000'),

If( [Item No.] = 'Count',

Num(Count(TOTAL [0-1M]), '#,##0.000'),

Num(Count(TOTAL [0-1M])/Count(TOTAL [0-1M]), '#,##0.000')))

Regards,

Jagan.

[sunny_talwar]

Hahahaha didn't think of this method and was not sure if making changes to the script was an option.

Thanks for sharing jagan‌

Sunny