
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Lookup() Chart Function
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
