3 Replies Latest reply: Jun 8, 2010 7:04 AM by jomand RSS

    Master Calendar and Sales

      Hello,

      I have one master calendar table which contains each date of 20 years and I have one table that contains the sales of each day the store was open. Now I want to create a table that shows each day of a selected month in the analysis, also if there are no entries in the sales table related to this day.

      I already tried it with the "Show all values" option but it doesn't work. I got all days in the table and not only the days that belong to the selected month. The days of other months had the value "0" but there were still shown.

      Any ideas?

      Kind regards,
      Jonas Manderscheid

        • Master Calendar and Sales

          Going to venture a guess here...

          Your dimension needs to be the day from your "floating" calendar and then your expression needs to have an if statement to only count a sale where the date of sale = floating calendar date. e.g.

          Dimension: [Calendar Date]

          Measure: sum(if([Sale Date] = [Calendar Date],[Sale Count]))

           

          Don't know if that helps?

          • Master Calendar and Sales
            Paul Ferguson

            Hi,

            Are you using a Chart to show the data?

             

            If this is the case there is a secondary setting under presentation tab of the properties,

            this is "Suppress Zero Values", i have had a similar problem with a relational calender and checking this box allowed all calender dates to be shown in the chart regardless to having no data relating to all dates.

             

            The other option is to realise the join to the calender table as you would in a regular SQL creating one table "FACTS" so to speak but this is dependant on your relation on date, one to one or one to many.

             

            Hope this helps

            Many Thanks

            Paul Ferguson

            • Master Calendar and Sales

              Hi,

              I made an example, may be it is possible to show the suggested answers there.

              @JonathanV: I don't have a floating calendar and I don't think this is a good way for my analysis. The date selection works for a lot of other charts. I just have this problem when I want to show all days of a month with the related sales data.

              @Paul Ferguson: I tried several things with these two options under the dimension and the presentation tab. Either I got all dates of all months although I selected just one month or I got just the dates of the selected month with related entries in the sales table.
              But could you show me your idea with the join? I would say my relation is many to many. Please look at the example.

              Thanks,
              Jonas Manderscheid