Average of Dimension

[Anonymous]

Hello QlikCommunity,

I'm having a problem with a spreadsheet and until now I haven't found a suitable solution in any forums.

Maybe you can help me.

To the situation:

I have a file that I want to evaluate products with.

Each product has a serial number.

On the one hand, I want the values ​​per product,

but I also want to be able to evaluate the average over a certain group.

An example:

Product      value

A100          5

A101          6

A102          5

A103          6

I am able to display this view, but in addition,

I would also like to be able to display this view:

Product      value

A1cum       5.5

A1cum should summarize A100 to A103 and display the average value.

Additionally I want to show these values in a Pivot-Table.

So A10x for example consists of B100 and C100:

Product  Part1    value

A100      B100    3

              C100    2

A101      B101    3

              C101    3

It should be possible to show these values in average, too:

Product     Part1       value

A1cum      B1cum     3

                 C1cum     2.5

It does not matter whether I need to use a new table chart for this, or about one

Button that needs to change formulas.

I hope you can help me and I would be very happy to receive an answer!

Best regards,

Markus

[jensmunnichs]

I think the easiest option would be to load in product categories in script. Should be fairly simple if your product codes and categories are as simple as in your example.

In the load of the table that contains the product codes, add*:

Left(Product, 2) & 'cum' as ProdCat

In the load of the table that contains the parts, add*:

Left(Part1, 2) & 'cum' as PartCat

Then just use the categories as dimensions in your pivot table, with avg(Value) as expression, and you should have what you want . If you have any questions or if I misunderstood your requirements, please let me know.

*Note you cannot use a field in a function in the same load that loads in the field. So if the loads for these tables are the first time you load in the required fields (Product and Part1), you will have to do a resident load and add the left function to that instead.