6 Replies Latest reply: Jun 20, 2014 9:27 AM by Bruno Souza RSS

    Sum data if a month

      Hi everybody,


      In my model, I have a fact table associated to a Calender dimension using a date. The Calendar dimension have some columns like date, week, month, year and many others. The fact table has one date number of visitor with some another dimension column.


      I would like to Sum the number of visitor only for the month corresponding to the largest date lying in the fact table.

      When I wrote a set analysis where I sum only for the last date using the MAX statement but I don't reach to do with the max month

      How could I do it please ?


        • Re: Sum data if a month
          Bruno Souza



          Try something like this:


          Sum({$<Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>} Visitors)


          Hope it helps.

            • Re: Sum data if a month

              Thanks for your answer Bruno.


              I've tested it. But it doesn't work. I don't why.


              In my statement, I'm working with week data. So my statement is

              Sum( {1< date = {">=$(=WeekStart(date))"} >} , Visitor)


              Please, I don't understand the second part of your statement

              Sum({$<Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>} Visitors)



                • Re: Sum data if a month
                  pradeep t

                  I am assuming that Visitor as Visitor_No (unique entry)

                  As you are counting the no.of visitiors,  need to use the function Count()  instead of Sum()...

                  • Re: Re: Sum data if a month
                    Bruno Souza



                    There is an extra comma in your expression:


                    Sum( {1< date = {">=$(=WeekStart(date))"} >} , Visitor)

                    You should remove it. And you used the field date directly inside the function WeekStart. It will only work if the user has select exactly one date.

                    About the second part of the expression, when you use a "" search in set analysis you can specify more than one condition. For example

                    Sum({1<Year = {">=2010<=2013"} Sales)

                    would return the Sum of all Sales from 2010 to 2013.

                    So, the second part o of the expression, since I used the {$} set, limit the upper bound to the max date selected. That way, the user could select a time period and see the number of visitors of the associated month (or week).

                    Please, take heed of Pradeep's comment and make sure of which expression we need: Sum or Count.

                    Since you seem to want the number of visitors from the most recent week only, regardless of the period selected, and your dates go all the way to 2020, you could try this expression:

                    Sum({1<date = {">$(=WeekStart(Today()))"}>} Visitor)

                    You'll have the the sum of the number of visitors from the beginning of this week until today.


                • Re: Sum data if a month

                  Please check the PFA may be it will fulfill your requirement.. !

                  • Re: Sum data if a month

                    I just notice something in the statement.


                    Let's imagine that there are many date in the Date Dimension, date from 2013/01/01 to 2020/12/31 and you have sales from 2013/04/01 to today. How do you write your SA statement knowing date if you just write MAX(date) you are referencing 2020/12/31 and not today ?