Date Calculation in Set Analysis

[shan_sriv]

Dear Experts,

I want to calculate Sum of Sales for Previous Year Q2 which is 01-Apr-2017 to 30-Jun-2017.

In data source I have column OrderDateTime which is having date and time like "01/01/2017 11:57:00". From this column I have created a new column OrderDate which is having only dates in DD/MM/YYYY format.

For above requirement I tried following Set Analysis expression but I am getting 0 as output :

=Sum({<OrderDate = {">=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY') <= Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY') "}>}Sales)

Could you please help me in correcting this expression ?

Thanks & Regards,

Shantanu

[rubenmarin · Accepted solution]

Hi Shantanu, try adding a dollar-expansion to set analysis, also maybe needs to ignore other selected values like Year:

=Sum({<OrderDate = {">$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY'))<= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}, YearField>}Sales)

Using a numeric date field in set analysis can help to avoid format issues, the numeric field can be created as:

Num(OrderDate) as NumDate

[YoussefBelloum]

Hi,

try this (you were missing the red parts):

=Sum({<OrderDate = {">=$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY')) <= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}>}Sales)

[shan_sriv]

Thanks a lot for your help Ruben.

[rubenmarin]

You are welcome.

Check if it should be:

=Sum({<OrderDate = {">=$(=Date(AddYears(QuarterStart(YearStart(Today()),1),-1),'DD/MM/YYYY'))<= $(=Date(MonthEnd(AddYears(QuarterEnd(YearStart(Today()),1),-1)),'DD/MM/YYYY'))"}, YearField>}Sales)

[YoussefBelloum]

keep in mind that with this, DAY 01/04/2017 will not be counted, so try to verify if you need it and so add this > to Ruben's expression