Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor II

Calculated subtotals

Hi experts,

in the attached example file, you'll find the problem I'm facing.

I want to create subtotals in a pivot, based on expressions with adding, subtracting, multiplying and dividing.


I'm not sure how to create these subtotals in a pivot table.

Should I create a dimension in the load script, in set analysis or should I create a new dimension as master dimension?


It would be great if you guys could help me out on this one!


Cheers,

Mike

7 Replies
MVP
MVP

Re: Calculated subtotals

Where are you hoping to see these subtotals? I am not 100% sure I understand your requirement completely

brunobertels
Valued Contributor

Re: Calculated subtotals

Hi

May be add first calculated dimension in the script :

=if(WildMatch(Number,'1','2','3','3B'), 'Field 1',

if(WildMatch(Number,'21','4'), 'Field 2',

if(WildMatch(Number,'97','89'), 'Field 3',

if(WildMatch(Number,'21','21A','89','97','4','4A'), 'Field 4'

)))) as FIELDS,

then in a straight table ( not pivot table )

add as dimension FIELDS

then as mesure :

=if(WildMatch(FIELDS,'Field 1'), sum(${Number=('1','2','3','3B')}Amount),

if(WildMatch(FIELDS,'Field 2'), sum(${Number=('21')}Amount)/sum(${Number=('4')}Amount)*100,

if(WildMatch(FIELDS,'Field 3'), sum(${Number=('97')}Amount)-sum(${Number=('89')}Amount),

if(WildMatch(FIELDS,'Field 4'), sum(${Number=('21','21A','89','97')}Amount) /

sum(${Number=('4','4A',)}Amount)*100

))))

Not sure it works , i can't test it in your app

mslottje
Contributor II

Re: Calculated subtotals

Hi Bruno,

first of all, thanks for your reply!

Unfortunately this doesn't solve my problem.

Attached you'll find a qvf file where I've created the dimension FIELDS in the load script and the given measure in the table/pivot table.

Perhaps you're able to test it now.

Kind regards,

Mike

mslottje
Contributor II

Re: Calculated subtotals

Hi Sunny,

I want to create a P&L in Qlik Sense and in the real data I have 95 different rows for the dimension Number (1,2,3A,3B etc. etc.). To get the proper results, I have to combine several values with formulas as given in the example file.

Next to that, I want to show the P&L in a pivot table so you can see the main totals (subs) first. If you need a more detailed overview, you're still able to see the specific values.

Could you clarify how you understood my introduction?

Mike

MVP
MVP

Re: Calculated subtotals

Would you be able to share your expected output in an Excel file?

mslottje
Contributor II

Re: Calculated subtotals

Hi Sunny (stalwar1‌ ),

attached a complete file, where the yellow marked rows are the subtotals.

In the third column you'll find the formula that is used to get the proper result.

The excel file differs from the qvf I've posted earlier today.

If there are any questions, please let me know!

MVP
MVP

Re: Calculated subtotals

It might be helpful if you can either send a expected output based on the sample provided, or update your sample so that we can use this expected output... variation is making it difficult to work on this.