Set analysis sum between two given dates

[harleen_singh]

Hello,

             i want to calculate sum between two given dates using set analysis. I am want to use greater than or equal to startdate and less than or equal to Enddate.

I am using this formula. but its not working even for 1 condition. How to use less than or equal to condition in ths formula??/

sum({$<OrderDate>={StartDate}>} Netamountpaid )

thanks

Lavi

[sbaldwin]

Hi, you need to add this into the set expression writing something like this:

sum({$<OrderDate={'<=$(=only(StartDate))'}>} Netamountpaid )

Thanks

Steve

[#3]

Using following expression

sum({$<OrderDate={">=StartDate<=Enddate"}>Netamountpaid)

[harleen_singh]

Steve,

             thanks for the response. in your formula what is StartDate??

is it variable or field name.

if it is variable then where to define it?

in my formula StartDate is field name

Regards

Harleen

[harleen_singh]

venkatreddy,

thanks for the response. in your formula what is StartDate??

is it variable or field name.

if it is variable then where to define it?

in my formula StartDate is field name

Regards

Harleen

[#6]

field Name

[sbaldwin]

Hi, yes i was also assuming it was a field name

Thanks

Steve

[swuehl · Accepted solution]

Hi Lavi,

venkatreddy's solution will work, but only if StartDate and EndDate are limited to one value in current selection.

For example, if a user selects none or more than one Date, the expression will not work.

You could combine the expression with a dollar sign expansion to handle date ranges:

= sum({<OrderDate= {">=$(=min(StartDate))<=$(=max(EndDate))"}> } Netamountpaid)

I assume you are not using StartDate and EndDate as dimensions in a table, with above expression, else you are running into troubles, since the set analysis will not care about the dimensions.

Regards,

Stefan

[Anonymous]

Hi Stefan,

Will the same expression works for current year and last year data as well, i tried below expression

For current year

Sum({<Year = {$(=max(Year))},Date={">=$(=min(Promo_Date_From))<=$(=max(Promo_Date_To))"}>} [Net Sales])

here am usning promo event as dimension.

it's giving 0 as output. Please suggest.

Regards,

Sadasiva

[Anonymous]

Hi Stefan ,

i m using a set analysis expression . in my report i m showing data for LMTD.  In Dimension i have LinkCommonDate and used:

=if(LinkCommonDate>=$(v1MonthMinDate),Date(LinkCommonDate))

where v1MonthMinDate is floor(Date(MonthStart( AddMonths( max(SurveyDate)   ,-1)),'YYYY-MM-DD'))

but when i m selecting a particular dealer it does not consider LMTD rule   and gives data for other months also.

How should i restrict my Date Dimension in report to show data for LMTD only .