topic Re: Combining values from multiple dimensions in one axis in QlikView

Combining values from multiple dimensions in one axis

Tue, 26 Apr 2016 11:19:40 GMT

Hi everyone,

I am trying to combine the values from multiple dimensions into one single axis in a bar chart. I think it's easier to explain with an example.

Let's say I have a data model with the sales of a company, and among others, I have geographical dimensions (let's say province) and product dimensions (let's say article). I can create two different bar charts, showing the total sales split among the different provinces and another one showing the sales split among articles. But is there any way I can mix the values of the two dimensions in the same graph? I also need to sort all the values by expression, regardless of whether it is a province or an article. So, for instance, what I want, in a table chart, should look like:

Dimension - Sum(Sales)

Province A - 10000

Province X - 9500

Article B - 8900

Province M - 8500

Article Z - 8000

Article D - 7000

Province B - 6500

Thank you for your help.

Re: Combining values from multiple dimensions in one axis

sunny_talwar — Tue, 26 Apr 2016 17:01:00 GMT

May be use a calculated dimension or combine the two in the script:

LOAD Province & ' ' & Product as TableDim

or a calculated dimension

Province & ' ' & Product

Re: Combining values from multiple dimensions in one axis

Wed, 27 Apr 2016 07:59:14 GMT

Hi,

Unfortunately this doesn't work. This system establishes a relationship between the two dimensions, and what I want is the values to be completely independent. What you suggest here would return something like this:

Dimension - Sum(Sales)

Province A Article A- 1000

Province X Article B- 950

Province Z Article B - 890

Province M Article M - 800

Province M Article Z - 800

Province A Article D - 700

Province B Article Z- 650

Not exactly what I need, which is:

Dimension - Sum(Sales)

Province A - 10000

Province X - 9500

Article B - 8900

Province M - 8500

Article Z - 8000

Article D - 7000

Province B - 6500

Re: Combining values from multiple dimensions in one axis

jagan — Wed, 27 Apr 2016 08:15:34 GMT

Hi,

Create one more additional dimension in your script by using

Data:

LOAD

Province & ' ' & Product as NewDimension

FROM DataSource;

Now use NewDimension field in the chart.

Hope this helps you.

Regards,

Jagan.

Re: Combining values from multiple dimensions in one axis

Wed, 27 Apr 2016 08:20:24 GMT

Hi,

Thank you for your reply but this throws the same results as the previous answer. Not exactly what I'm looking for, as I explained in the previous message.

Cheers,

Ignasi.

Re: Combining values from multiple dimensions in one axis

sunny_talwar — Wed, 27 Apr 2016 15:25:53 GMT

Would you be able to share some raw data behind the output you are expecting to see. I am not sure how the data is structured and that is why wasn't sure what you wanted.

Re: Combining values from multiple dimensions in one axis

Wed, 27 Apr 2016 15:37:56 GMT

The data is complex but for the sake of simplicity please assume we have the following input:

My desired output table would look like:

Dimension ... Sum(Sales)

Province 2 ... 120

Article C ... 110

Province 1 ... 90

Article B ... 70

Article A ... 30

I hope this example helps understand what I'm trying to achieve. The real scenario is more complex, with more than two dimensions and several tables involved, but if someone can help me work out this simple example, I guess I can apply the same method to a bigger scenario.

Re: Combining values from multiple dimensions in one axis

jagan — Wed, 27 Apr 2016 16:19:56 GMT

Hi,

You have to try like this using Concatenate

Data:

LOAD

Article AS Dimension,

Sales

FROM DataSource;

Concatenate(Data)

LOAD

Province AS Dimension,

Sales

FROM DataSource;

Now use Dimension field as dimension in your chart.

Hope this helps you.

Regards,

Jagan.

Re: Combining values from multiple dimensions in one axis

Wed, 27 Apr 2016 16:26:02 GMT

The problem is that the real scenario has a lot more dimensions, and I was trying to avoid using only one field in the script - that would be really bad for other purposes of the model. So my question is if there is any way I can do this without merging all the dimensions into one in the script load.

Re: Combining values from multiple dimensions in one axis

jagan — Wed, 27 Apr 2016 17:12:13 GMT

I think it is not possible without merging into a single field.

Regards,

Jagan.

Re: Combining values from multiple dimensions in one axis

sunny_talwar — Wed, 27 Apr 2016 17:14:28 GMT

Jagan what do you think about a Link Table here? Do you think that can work?

Re: Combining values from multiple dimensions in one axis

johnw — Wed, 27 Apr 2016 19:44:54 GMT

Attached is one way to do it without touching the data model since you don't want to do that. I used $Field as a hidden dimension in the chart, then picked the right field based on the value of $Field. I believe it could be easily extended to as many dimensions as you want to use.

Edit: There's no reason for the hidden dimension of $Field. That does nothing for us.

Dimension 1: $Field
Dimension 2: =pick(match($Field,'Article','Province'),[Article],[Province])
Expression: sum(Sales)

Re: Combining values from multiple dimensions in one axis

sunny_talwar — Wed, 27 Apr 2016 19:46:35 GMT

Amazing idea

I am glad you are back

Re: Combining values from multiple dimensions in one axis

Thu, 28 Apr 2016 08:05:41 GMT

That was genius. It works like a charm. Thank you very much!

Re: Combining values from multiple dimensions in one axis

jagan — Thu, 28 Apr 2016 15:05:16 GMT

With link table also we can achieve.

Re: Combining values from multiple dimensions in one axis

jagan — Thu, 28 Apr 2016 15:05:31 GMT

Good idea john.

Re: Combining values from multiple dimensions in one axis

Anonymous — Fri, 02 Sep 2016 22:15:19 GMT

johnw‌ How to create a list box for this new dimension for selection? I made a list box with same expression =pick(match($Field,'Article','Province'),[Article],[Province])

but when I select in this list box, the bar chart says 'no data to display'.

Please advise.

Thanks,

BR,

SAK

Re: Combining values from multiple dimensions in one axis

johnw — Wed, 12 Oct 2016 20:17:03 GMT

Sorry for being so incredibly slow to get back to this.

Seems like our basic problem is that "When using more than one field as base for a calculated field, the logical implications of selections in the base fields or the calculated field may be quite complex." Complex, sure, but without any further explanation of how QlikView decides what you're trying to do with the underlying fields, I'm reduced to trial and error. In any case, the expression I suggested picks a single value for the field you click on, and ALL values for the other field. That's fine if you only select in one or the other, but if you select any values from both, you get ALL values from both. Not good.

Maybe a month back, I tried hard to find a data model solution for this, and failed. I have quite a few applications where I do something exactly like this. In all of them, there's an island dimension that I load all the values from both fields into. Then I have macros fired that go back and forth, keeping the island dimension and the two real dimensions in sync. I hate that utterly inelegant solution. Converting to actions without macros wouldn't really be much better. I want to replace that solution. So I gave it a shot and failed.

We can move a little of the complexity into the script by building a Seq field with values 1 and 2. I wanted to use valueloop, but "Purely synthetic calculated fields created via the valuelist and valueloop functions are not permitted." Anyway, then we can use:

pick(Seq,Article,Province)

But that works no better in a list box. Same behavior as the original expression. So we're looking for an expression that produces the full list, and for which QlikView interprets selections as desired. I poked at it for about an hour, and wasn't able to come up with anything. Either what I did disconnected the list box entirely from the actual fields, or the selection behavior was the same as the original expression.

So the only practical way to do it that I know of at this time is triggered actions or triggered macros working on an island field that combines the values of both fields. I hope someone comes up with something better.

Re: Combining values from multiple dimensions in one axis

raju_salmon — Sun, 14 Oct 2018 05:37:41 GMT

Is there anyway we can have totals Articals (as line 4) and Province (last line) and if possible one empty row between both?

Re: Combining values from multiple dimensions in one axis

raju_salmon — Sun, 14 Oct 2018 05:40:04 GMT

Is there anyway we can have totals Articals (as line 4) and Province (last line) and if possible one empty row between both?