Change If Then to Set Analysis using Dates

[#1]

Any idea how to change this (Functioning)  If/Then into set analysis:

=sum(If(SaleDate>=DateEffStart and SaleDate<=DateEffEnd  //Sale date of the item has to be within the effective dated start and end

   and SaledDate >= vStartDate and SaleDate <= vEndDate, Amount))  //and the saledate also has to be within the input start/end date variables.

So SaleDate, DateEffStart and DateEffEnd are all fields in the database.   SaleDate is the date of the sale.

Thanks very much!!

[alexandros17]

Sum({$ <SaleDate={'>=$(DateEffStart)<=$(DateEffEnd)'},SaleDate={'>=$(vStartDate )<=$(vEndDate )'}> Amount}

[Anonymous]

Try this (check the parenthesis):

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> * <$(='>='&date($(vStartDate))&'<='&date($(vEndDate)))"}>} Amount)

[#4]

When I plugged in the expression, everything up to the * was fine, but something was wrong with the rest.  (there was a red syntax underline under everything after the *< .

Is something slightly off there?  Thanks very much!

[#5]

I tried this and it summed up all values for every row.  Didn't seem to pay any attention to any of the date delimiters.

Any other ideas?  Thanks you!

[Anonymous]

The syntax red lines are often misleading, especially in sets.  But possibly I mistyped something.  Anyway, try these two parts separately first:

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> Amount)

and

sum({<SaleDate={"$(='>='&date($(vEndDate))&'<='&date($(vEndDate)))"}> Amount)

just to check if the both parts are ok.

[#7]

They the saledate between the start and end dates works fine.

The Saledate between the effective dated start and end just totals up all values for everyone.  But it does give a result. 

[Anonymous]

In this case this should give a result (I certainly mistyped in my earlier post, wrong copy/paste 😞

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> * <SaleDate={"$(='>='&date($(vEndDate))&'<='&date($(vEndDate)))"}>} Amount)

The fact that result "between the effective dated start and end just totals up all values" doesn't tell much without seeing the application.  The syntax may be correct but logic may be not...

Regards,

Michael

[#9]

Thanks Michael.  No luck.  I modified the test file so I can upload it.  I know that makes it a LOT easier so here it is.  Thank you again for looking at this for me.

[Anonymous]

I'm not good in copy/pasting

Notice that in the second part I used vEndDate twice.  Just replace the first one with the vStartDate:

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> * <SaleDate={"$(='>='&date($(vStartDate))&'<='&date($(vEndDate)))"}>} Amount)