Forum Discussion

Thimios's avatar
Thimios
Helper III
6 years ago

Cumulative value for dynamic date range

I have a Calendar table and two more tables: 1. Offer table w/columns: Offer code, Customer code, Offer value. 2. Opportunity table w/columns: Opp code, Opp Start Date, Opp End date, Customer code ...
Need Help
Thimios's avatar
Thimios
Helper III
6 years ago

Thank you ImkeF for introducing the cartesian concept to me. This does not help though as all offers will be associated with all opportunities and I cannot see how to filter.

amitchandak's avatar
amitchandak
Super User
6 years ago

Thimios ,

Can you share sample data and sample output. Make me @

Appreciate your Kudos.

  • Thimios's avatar
    Thimios
    Helper III
    6 years ago

    Let me rephrase the case, so I can make it clearer:
    Let's assume that I have typical Calendar table and a fact table with the following columns:

    - Opportunity code (text field)

    - Opportunity start date (date field)

    - Opportunity end date (date field)

    - Opportunity value (currency field)

    I need to create a column chart with Calendar Hierarchy in the X axis and Values on the Y axis where each column will show the total value of opportunities that were open at that date (that is when date is between Opp start date and Opp end date).