Lookup() Chart Function

wcilliers — Sat, 16 Nov 2024 17:28:17 GMT

Hi All,

I am in need of a solution similar to the lookup()script function, but for charts. Pretty much like Excel VLookup.

I have a Sales Fact table and two dimension tables, Customers and Products.

I also have a special pricing table for customers on each product. I want to be able to display the special price for each product by customer, obviously also against sales and other measures.

I have tried using (Only) but this doesn't work unless only one product and customer is selected. I also though of doing an ApplyMap() on the Fact table creating a key from Customer&Product Codes. But this loses the visibility on special pricing for products that has never been sold for that customer.

Any ideas would help please?

Thanks,

Wynand

Re: Lookup() Chart Function

cheenu_janakira — Thu, 25 Mar 2021 20:37:25 GMT

Hi Wynand,

It's difficult to picture without a concrete example, but would something of this kind work?

Create 2 new columns which are exact replicas of CardCode inside the SpecialPricing and Customers tables, so that you have 2 additional fields in both tables, e.g. CardCode_SP and CardCode_Cust.

Then use these fields as "pseudo-filter" with set analysis, e.g.

SUM({$<CardCode_SP = {$(=CHR(39) & Concat(DISTINCT CardCode, CHR(39) & ',' & CHR(39))&CHR(39))}, CardCode=>} Blahblah)

In this case there won't be any data reduction due to selections on CardCode field, but you are parsing the values selected in CardCode and putting them through to CardCode_SP as a pseudo-selection.

Hope this makes sense and might lead you in the right direction.

Otherwise, a concrete sample file/example would be great for debugging.

KR,

Cheenu

topic Lookup() Chart Function in App Development

Lookup() Chart Function

Re: Lookup() Chart Function