5 Replies Latest reply: Oct 24, 2017 2:11 AM by Court van de Lisdonk RSS

    Date problem in combination with Sales and Budget tables

    Court van de Lisdonk



      I have a Date problem in combination with my Sales and Budget tables

      I have a table TaskForcePlan with my budgetvalues  per day. I have also a table Sales with my salesvalues per day. In fact I have two measure or main tables.


      I have made a straighttable with dimension DateTaskForcePlan from the table TaskForcePlan and three expressions:


      One for Budget: Sum({$ < Year = {$(= max(Year))} > } (PlanProductBasic + PlanCorrProduct))


      One for Sales: Sum({$ < Year = {$(= max(Year))} > } ProductValue)


      One for the difference: Sum({$ < Year = {$(= max(Year))} > } ProductValue) -
      Sum({$ < Year = {
      $(= max(Year))} > } (PlanProductBasic + PlanCorrProduct))


      When I combine both tables with my calendar Daygrid I have three options


      First option is to define the date-fields in both tables as Date

      Second option to define the date-field in my budgettable as Date and define  a key (company-date) between both tables

      Third option to define the date-field in my salestable (facttable)  as Date and define  a key (company-date) between both tables


      The first and second option has as result that I see al dates with budget and/or sales, as example is 15/06/2017 which has a budget but no sales. Other result is that when I select Oct. as month I see all budget till the end of month.


      The third option has as result that I only the dates when there are sales for that day, so for Oct. I don’t see all budget till the end of the month, but days with budget and no sales like the example 15/06/2017 are not shown either. So the values for the month Juni are not correct.


      I have added two QVW’s, one with the first option and the second with option three.


      What I need is a straight table showing all dates with values for Budget and/or Sales and where in the current month only until the current day is shown: so when no month is selected all until today, when a month is selected only the days of that month and when October is selected all days with values until today.:


      Examples :

      1. 11/03/2017 has no budget but sales: so day has to be shown
      2. 15/05/2017 has budget and sales: so day has to be shown
      3. 15/06/2017 has budget but no sales: so day has to be shown
      4. 30/10/2017 has budget, no sales but lays in the future: so day has not to be shown


      When a day has no budget and no sales values like in weekends, that day should not be shown. 

      I tried to add a key table with the different Dates as keyfields, but that gives the same result as described in Option one. I made one big table by using two left join where my daygrid was leading, but that costs performance and is no solution for Problems like example 4.  I also looked in other questions/solutions here, but I didn't find a real solution for my Problem.  Perhaps a change in my expressions can fix my problem, I don't know.


      So I hope someone can tell me which option is the best and how I should change my dimensions, expressions and relations between the tables to get the straight table I need (as described).


      Thanks in advance