Sum data if a month

[#1]

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 ?

Thanks.

[Anonymous]

Hello,

Try something like this:

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

Hope it helps.

[#3]

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)

Thanks.

[PradeepReddy]

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()...

[#5]

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

[#6]

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 ?

Thanks.

[Anonymous]

Hi

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.

Regards.