topic Re: show value for different dimention in graph in QlikView

show value for different dimention in graph

bert_geraerts — Mon, 10 Dec 2018 11:27:14 GMT

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?

Re: show value for different dimention in graph

karthikoffi27se — Mon, 10 Dec 2018 11:41:46 GMT

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

Re: show value for different dimention in graph

bert_geraerts — Mon, 10 Dec 2018 12:28:12 GMT

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

Re: show value for different dimention in graph

Zhandos_Shotan — Mon, 10 Dec 2018 12:59:20 GMT

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.

Re: show value for different dimention in graph

bert_geraerts — Mon, 10 Dec 2018 13:32:16 GMT

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?

Re: show value for different dimention in graph

Zhandos_Shotan — Mon, 10 Dec 2018 13:38:47 GMT

Then use IF condition, like this:

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

Re: show value for different dimention in graph

bert_geraerts — Mon, 10 Dec 2018 13:46:13 GMT

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))

Re: show value for different dimention in graph

Zhandos_Shotan — Tue, 11 Dec 2018 06:25:47 GMT

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%20of%20Aggregate%20Qualifiers.htm

Did you tested thats expression:

avg(TOTAL<%OrigItem> AvgSales)

Re: show value for different dimention in graph

bert_geraerts — Tue, 11 Dec 2018 08:32:21 GMT

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!

Re: show value for different dimention in graph

bert_geraerts — Tue, 11 Dec 2018 08:51:25 GMT

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.

Re: show value for different dimention in graph

Zhandos_Shotan — Tue, 11 Dec 2018 10:00:39 GMT

Finally i get what you want!

Here is decision:

if(%Item2<>%OrigItem,
sum({<%Item2=P({1}%OrigItem)>} total<%OrigItem> AvgSales),
sum(AvgSales)
)

and qvw file.

Re: show value for different dimention in graph

bert_geraerts — Tue, 11 Dec 2018 10:26:27 GMT

Perfect, thx !