topic How do create expression that is dependent on value of Dimension? in QlikView

How do create expression that is dependent on value of Dimension?

tomdabek — Fri, 26 Oct 2018 18:36:01 GMT

How can you show the degree or amount of vertical integration in a qlikview chart/table?

Lets say you have a case where you have companies that make shoes and also the leather to make the shoes.

We have Company1, Company2, Company3 etc...

They all sell shoes and many also sell leather to make shoes. I would like a resulting chart/report that looks like this:

Company	Shoe Sales $$	Value of Leather sold by Company 1	Value of Leather sold by Company 2
Company 1	5000	500	0
Company 2	3000	0	1500
Company 3	9000	100	2000
Company 4	2000	0	0

The underlying data table looks something like:

Shoe Seller	Leather Supplier	Shoe Value	Leather Value	Shoe Buyer
Company 1	Company 1	2500	500	Ontario
Company 1	Unknown	2500	500	Ontario
Company 2	Company 2	3000	1500	Quebec

I have having trouble writing an expression for columns 3 and 4 in the first table/report.

How do i write an expression for the Leather Value with a condition that is determined by the value of the dimension/ 1st column?

Any help appreciated

Thanks

Re: How do create expression that is dependent on value of Dimension?

Jesh19 — Fri, 26 Oct 2018 18:51:16 GMT

Add Shoe Seller as your dimension.

Add the below 3 as expressions.

Shoe $$ - Sum([Shoe Value])

Value of Leather Sold by Company 1 = Sum({<Company = {'Company 1'}>}[Leather Value])

Value of Leather Sold by Company 2 = Sum({<Company = {'Company 2'}>}[Leather Value])

This should help with your solutions

Re: How do create expression that is dependent on value of Dimension?

tomdabek — Fri, 26 Oct 2018 19:25:37 GMT

That doesn't work. In addition, this set analysis - Sum({<Company = {'Company 1'}>}[Leather Value])

you still need something to indicate that not only is the Company= Company1 but that the Leather Supplier is ALSO company 1.

In your example the output would show that the report column 'Value of Leather sold by Company 1' would ALWAYS be 500, for every row in the report because there is no check about the shoe seller.

Re: How do create expression that is dependent on value of Dimension?

Jesh19 — Fri, 26 Oct 2018 19:37:59 GMT

Sorry, I gave you the wrong expression.

Please try the below expressions:

Value of Leather sold by Company 1 -

Sum({<[Leather Supplier]={'Company 1'}>}[Leather Value])

Value of Leather sold by Company 2 -

Sum({<[Leather Supplier]={'Company 2'}>}[Leather Value])

Re: How do create expression that is dependent on value of Dimension?

tomdabek — Fri, 26 Oct 2018 19:44:46 GMT

This doesn't work because it always gives the same value in every row.

The output needs to be the value sold by Leather Supplier 1/2/3 to a particular shoe seller.

Your statements correctly identify the Leather Supplier but need an additional condition something like [Leather Supplier] = [Shoe Seller] and the difficulty is that the [Shoe Seller] is unknown - it is dynamic based on the dimension column