topic Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row in App Development

Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

vrk2024 — Thu, 25 Apr 2024 02:26:56 GMT

All,
I am new to Qlik and wondering if this is possible to acheive in qlik sense.
Requirement:

sum consecutive rows in a pivot table and add it as another/new row.

Please see the attached for sample data reference.

For example in the attached qvf .. i want to sum MMR + NAT i.e 10000+670 and add the sum 10670 as a new row below it .

MMR

NAT
MMR+NAT
PC
TAR
ULP

I want to do this for multiple rows and the data set will be static data set .

Please let know if the requirement is not clear, appreciate any help or guidance on this.

Thank you,
RV

Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

jchx — Thu, 25 Apr 2024 07:36:53 GMT

You could use pick wildmatch to create a new dimension group (ITEM_GRP) then add it to the pivot table. Gotta switch up the presentation settings too (See screenies)

=pick(wildmatch(
ITEM
,'MMR'
,'NAT'
,'PC'
,'TAR'
,'ULP'
)
,'MMR+NAT'
,'MMR+NAT'
,'PC'
,'TAR'
,'ULP'
)

Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

vrk2024 — Thu, 25 Apr 2024 11:12:59 GMT

@jchx thank your for the quick response, i will try this and update.

Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

vrk2024 — Thu, 25 Apr 2024 21:15:13 GMT

@jchx

Hi,
Sorry i am new to Qlik Scripting and not sure if i am doing it right. I tried to write the below expression but unofrtunately i am not getting the expected output. Please could you take a look at the attached qvf.

I have created the ITEM_Grp new dimension using the below expression.

=pick(wildmatch(
ITEM
,'CAL'
, 'DAR'
, 'DAT'
, 'KCM'
, 'KLM'
, 'LAG'
, 'LAT'
, 'LLM'
, 'MAR'
, 'MKF'
,'MMR'
,'NAT'
,'PC'
,'TAR'
,'ULP'
)
,'CAL'
, 'DAR'
, 'DAT'
, 'KCM'
, 'KLM'
, 'LAG'
, 'LAT'
, 'LLM'
, 'MAR'
, 'MKF'
,' MMR+NAT'
,'MMR+NAT'
,'PC'
,'TAR'
,'ULP'
)

Expected output

I want to sum consecutive rows like MMR+NAT and add a new row. I want to perform this on multiple selective rows. eg:

MAR + MFK (3000+45=3045)

MMR+NAT (10000+670=10,670)

Please could you help with the syntax for the same. I really appreciate your help.

Please see the attached qvf for reference.

Thank you,

Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

jchx — Fri, 26 Apr 2024 02:02:26 GMT

Just do the same in the pick wildmatch for other rows you want to sum up:

=pick(wildmatch(
ITEM
,'CAL'
, 'DAR'
, 'DAT'
, 'KCM'
, 'KLM'
, 'LAG'
, 'LAT'
, 'LLM'
, 'MAR'
, 'MKF'
,'MMR'
,'NAT'
,'PC'
,'TAR'
,'ULP'
)
,'CAL'
, 'DAR'
, 'DAT'
, 'KCM'
, 'KLM'
, 'LAG'
, 'LAT'
, 'LLM'
, 'MAR + MFK'
, 'MAR + MFK'
,' MMR+NAT'
,'MMR+NAT'
,'PC'
,'TAR'
,'ULP'
)

The first part of the expression picks up the original variable and the later part buckets it into the respective new categories that you've created.

Re: Pivot Table- sum consecutive rows in a pivot table and add it as another/new row

vrk2024 — Fri, 26 Apr 2024 20:12:44 GMT

@jchx thank you appreciate your help.