Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

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

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:

CompanyShoe Sales \$\$Value of Leather sold by Company 1Value of Leather sold by Company 2
Company 150005000
Company 2300001500
Company 390001002000
Company 420000

0

The underlying data table looks something like:

Shoe SellerLeather SupplierShoe ValueLeather ValueShoe Buyer
Company 1Company 12500500Ontario
Company 1Unknown2500500Ontario
Company 2Company 230001500Quebec

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

4 Replies
Creator II

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

Contributor III
Author

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.

Creator II

Sorry, I gave you the wrong expression.

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

Contributor III
Author

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

Community Browser