Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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