Totaling a dimension

[hopkinsc]

Hi,

Hope someone can help me with this problem...

I have a PIVOT TABLE with various dimensions in. Here is what it looks like...

Against every TRANS ID there are product sales. These product sales are listed individually so the user can see what was purchased as part of the transaction.

I have created an expression which calculates the sales value - discount, which results in every line having an ACTUAL VALUE against it. e.g.

the top line states that on Trans ID 2392028 a packet of WALKERS WORCESTER SAUCE CRISPS with the value of £0.52 and a discount of £0.06 giving a ACTUAL VALUE of £0.46.

As you can see there were also 2 other products bought in this transaction (Diet Coke and a Cheese and Onion Sandwich) which also have an ACTUAL VALUE against it.

What i am trying to do is total the actual value per Trans ID. so instead of the first Trans ID displaying £0.46, £0.99, £1.55, it would total them up to display £3.00.

But to make it a bit more user friendly, i would only want it displayed once rather against the 3 products.

Does this make sense??

Any help would be appreciated... 🙂

[Oleg_Troyansky]

First of all, make sure that you use an aggregation function in your expression:

sum(SalesValue - Discount) and not just SalesValue - Discount.

Now, you need to decide what look and feel do you need:

1. If you want to have a single line per Transaction, then simply remove all the unnecessary Dimensions - like Product ID, Description, etc.

2. If you want to see all the detailed lines but also have a subtotal by Transactions, then add a flag "Show Partial Sums" at the Presentation tab for the Product Code (it's the dimensions next to the Trans. ID). QlikView will add subtotals to the table.

cheers, and welcome to QlikCommunity!

[hopkinsc]

Hi, thanks for your reply...

That worked great, It would be nice to hide the individual ACTUAL VALUES though so i just have a total, is that possible?

Also, the PIVOT TABLE has a fast change option to make it a STRAIGHT TABLE, can the same thing be done for that as i can't see a SHOW PARTIAL SUMS check box in the straight table properties?

[Oleg_Troyansky]

For the first question - I'm a bit confused... Do you want to show the detailed Dimensions (Item Number, Dimension, etc...) but hide the numeric values? This sounds a bit strange, but if this is, indeed, what you want - you can use function Dimensionality() to determine at what level of detail you are, and suppress the numbers at the lowest level of detail. If, on the other hand, you don't want to see any details - then just remove the unnecessary Dimensions from the chart, and the numbers will get aggregated.

For the second question - no, the main difference between Pivot Table and Straight table is that Pivot Tables allow grouping and subtotals, while straight tables - don't. In this particular case, using Fast Change and turning the table into a Straight table probably doesn't make much business sense...

[hopkinsc]

Hi, i want to hide the individual actual values for each item but instead only show a total per transID. We don't need to know what each item cocts but we do need to know the total transaction value.

If i remove the expression (Actual Value) then i don't get a total at all.

[Oleg_Troyansky]

You should use function Dimensionality() to determine when to show and when to hide the data. For example:

if(Dimensionality() >=6, null(), sum(Sales))

[hopkinsc]

Hi Oleg,

Thanks for all your help. i really appreciate it. The last example works great, but i am wondering what the 6 relates to? i know its only an example, but i don't understand DIMENSIONALITY >=6. I have played around with changing the number 6 to various other numbers, but they display the figures that i don't want to see.

Can you briefly explain the dimentionality >6??

[#8]

Hi,

Dimensionality() is a chart function which gives you that at which level you are right now in a pivot table.

for example if you have 3 dimensions in you pivot table the lowest dimensionality() will be 3. if you are at subtotal level for the third dimension then the dimensionality() is 2 and so on.....

for more details you can read qlikview help "dimensionality"

Thanks & Best Regards,

Kuldeep Tak

[hopkinsc]

Thanks for your explanation 🙂