Question on distinct count for each value
Hello,
our business users display campaigns to their customers. When they buy a contract they get a discount. Sometimes a campaign contains more than one discount (i.e. monetary discount and data volume). Then the discount table contains 2 rows for a campaign.
In our data model we have a fact table with sales and activations on discount level. the discount is joined to the campaign table and the contracts. The business user wants to ba able to count sales and activations on discount level (1 activation per discount) and also on campaign level (1 activation per campaign, no matter how many discounts)
The difficulty is: The table contains multiple rows for a contract and a discount, when i.e. activation and sales are on different dates/ months (contract is bought 02.01.2023 but activated 23.01.2023). You find a picture of the data model attached.
In SAP BO we solve it like this:
we have a variable that looks up (for each contract and campaign) if there is an activation and then makes a distinct count on the contract number
Is there a solution for it in pyramid so we are flexible to use a measure on campaign or discount level and not duplicate the value?
3 replies
-
Hi Janine,
Its a little hard to follow your exact scenario. It would be very helpful if you could create a simple sample excel with a matching Pyramid model and discovery report that represents the challenge you are facing. It will be easier to help you from there.
Thanks,
Ez
-
Have you tried using distinct count measures and bidirectional joins in the model design?
-
I managed to solve the problem. I aggregated the Table on campaign level and chose the maximum value for activationsper campaign and contract. Then I changed the join path from discount to campaign. Even in combination with our second fact table which is still joined with discount, Pyrahna Engine delivers the correct values