Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've got a table with two dimensions and several formulars.
I have to Display one additional row, which displays the sum of two other rows.
See attached file. The red row I have to insert, which contains the data of the two above.
Has anybody an idea?
Thanhx!
you want to have sub total for each Country ?
Hi Martina,
without any knowledge of the data behind I can only guess.
If you try to reasonbly transfer this into your script you'll get an additional owner Gnewas & Almara:
T1:
Load
Country,
Owner,
Product,
Amount
From your source.qvd;
T2:
Load
Country,
‘Gnewas & Almara’ as Owner,
Product,
Amount
Resident T1
where Owner = ‘Almara’ or Owner = ‘Gnewas’;
The second Table automatically concatenates to T1 and you’ll have an additional owner Gnewas & Almara with all the products and amounts of Gnewas and Almara, placed in Indonesia.
Hope this helps
Burkhard
Hi,
You can go for some thing Profit and Loss statement dashboard in qlikview or create the Interval match table if required to you.
How to Create a Profit and Loss Statement in QlikView
Regards,
Anand
Not a subtotal for each Country, only the marked line is required.
Thanks for your replay, but I can't change the script in the recommended way, because it is very complex, several pages, a lot of data.... and the results in a lot of tables.....
The table I posted is only one and this one is reduced to the relevant Information.
Thanx, I can't see a relation between P&L and my data.
But maybe the interval match table could help. What do you mean with this table?
They are used to calculate the matched record start and end numbers only to get the total sum values.
Regards
Anand
Ok, Martina.
Then we try it in a different way.
1. Step
add this data island to your script (no changes to existing script):
CalcDim:
LOAD * INLINE [
Dimension
1,
2
];
and perform a reload.
2. Step
(I assumed your table is a pivot table)
Create this calculated dimension in the dimension tab of your table:
=If(Country = 'Indonesia', Pick(Dimension, Owner, 'Gnewas & Almara'), Owner)
delete your old dimension 'Owner' and rename the new dimensin 'Owner' again.
Go to the Sort tab and check for 'Owner' sort by expression. Enter this expression
If(Country = 'Indonesia', Match(Pick(Dimension, Owner, 'Gnewas & Almara'), 'Gnewas', 'Almara', 'Gnewas & Almara', 'Berewic'), Owner)
Chose 'Ascending'.
Go to the expression tab and enter this expression for No of Products (I used the basic expression 'Sum(Products)' you should take your expression instead):
If(Country = 'Indonesia',
If(Dimensionality() = 1, Sum({$<Owner = {'Gnewas', 'Almara', 'Berewic'}>} Products), //this gives the subtotal of Indonesia
Pick(Dimension, Sum(Products), Sum({$<Owner = {'Gnewas', 'Almara'}>} Products))), //this gives the value of Gnewas & Almara
Sum(Products)).
At last you'll get this result:
You'll find the extra row and the subtotal of Indonesia is unaltered.
Hope this is ok for you
Burkhard