show value for different dimention in graph

[bert_geraerts]

Can someone help me with the following issue that I'm having:

We have a list of products, that contain the following data:
%OrigItem, %Item2, Description

1304745, 1304745, TEST PROD A
1304745, 1304913, TEST PROD B

%OrigItem is the original productnumber.
%Item2 is the replacement productnumber for %OrigItem.

In a different table we have the stock (=Stock) + average sales (=AvgSales) for each %Item2 and I would like to show the following in a tablechart:

1304745, 1304745, TEST PROD A, sum(Stock) product 1304745, sum(AvgSales) product 1304745
1304745, 1304913, TEST PROD B, sum(Stock) product 1304913, sum(AvgSales) product 1304745

So for the replacement product B, I still want to show the total sales for product A.

How can this be done using set analyses?  

[karthikoffi27se]

Hi Bert,

use the below formula in expression.

Sum({<%Item2 = {'1304745'}>}Stock)

Sum({<%Item2 = {'1304745'}>}AvgSales)

Sum({<%Item2 = {'1304913'}>}Stock)

Sum({<%Item2 = {'1304745'}>}AvgSales)

Many Thanks

[bert_geraerts]

I already tried that, but it is not showing any result for PROD B.

[Zhandos_Shotan]

Hi!

Try this:

%OrigItem, %Item2, Description, exprStock, exprAvgSales

1304745, 1304745, TEST PROD A, sum(Stock), avg(TOTAL<%OrigItem> AvgSales) product 1304745
1304745, 1304913, TEST PROD B, sum(Stock), avg(TOTAL<%OrigItem> AvgSales) product 1304745

I mean use Total by OrigItem field for second expression, and Avg, not sum.

[bert_geraerts]

Almost got it, when I use the following formula I get the correct result for both lines --> 55

avg(TOTAL{<%Item2 = {'1304745'}>}AvgSales)

But when I try to integrate this with the existing fields, I get the wrong result --> 44
I only need this when %OrigItem <> %Item for that line.

avg(TOTAL{<%Item2 = {'=%OrigItem'}>}AvgSales)

Any more advise?

[Zhandos_Shotan]

Then use IF condition, like this:

If (%OrigItem <> %Item,   expression1,   expression2)

[bert_geraerts]

That doesn't help, since that doesn't change the result.

In the example below I have entered a third product.
Using the provided formula it will always give me the overall average for all lines.

if(%OrigItem <> %Item2, avg(TOTAL{<%Item2 = {'=%OrigItem'}>}AvgSales), sum(AvgSales))

But when I use the fixed number, the result is perfect.

if(%OrigItem <> %Item2, avg(TOTAL{<%Item2 = {'1304745'}>}AvgSales), sum(AvgSales))

[Zhandos_Shotan]

avg(TOTAL{<%Item2 = {'=%OrigItem'}>}AvgSales)

..that expression is not correct. You using  set analysis expression and TOTAL by all table records, not by field.

Correct syntax for Totals by field values: Total <FieldName> . See example 2 below:

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Examples%20o...

Did you tested thats expression:

avg(TOTAL<%OrigItem> AvgSales)

[bert_geraerts]

Zhandos,

I was using set analysis because we want to show the total sales for a different dimension.
If I follow you're advice, I get an average for both %OrigItem ,since there are always 2.

However you might have given me an idea, I need to add another field to my table, that shows %origItem only when it is the replacement-nr.  Then I can use the "TOTAL"-formule and it should work.

I was thinking that maybee it was possible to do this with set analysis, but apparently it isn't.

Thx for the assistance!

[bert_geraerts]

I was wrong, this still shows me the avg value for my original dimension and not the dimension I want. So I keep ending up with the same problem.

If there isn't any other sollution then I'm afraid that the only thing I can do is to add a complete new dataset linking to the replacementnr. This however involves a lot of data, that i was trying to avoid.