Calculated subtotals

[Anonymous]

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

[sunny_talwar]

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

[brunobertels]

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

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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!

[sunny_talwar]

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.