Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
This is what I meant earlier "The syntax may be correct but logic may be not..."
You're using this expression in the chart, assuming that the rows are aggregated per dimensions. But set modifiers doesn't know anything about dimensions, they look at the overall data set.
Doesn't look to me as a place for set analysis.
Sum({$ <SaleDate={'>=$(DateEffStart)<=$(DateEffEnd)'},SaleDate={'>=$(vStartDate )<=$(vEndDate )'}> Amount}
Try this (check the parenthesis):
sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> * <$(='>='&date($(vStartDate))&'<='&date($(vEndDate)))"}>} Amount)
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!
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!
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.
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.
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
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.
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)