Sum Amount when Date equals to SalesDate

[wanyunyang]

I have two tables in the script: Calendar table with field Date, Sales table with fields SalesDate and Amount. I cannot rename and link the two date fields which will ruin the model.

I want to sum Amount when Date equals to SalesDate. My function now is:

Sum(Aggr(If(Date= SalesDate, sum(Amount), 0), Date, SalesDate))

This works. And my question is: how can I write an expression with set analysis? Something like:

Sum({<Date=SalesDate>}Amount)

Thanks in advance!

[rubenmarin]

Hi Wanyum, when you say Date equals to salesdate.. if it's bacause it's a table and each row has a Date, note that set analysis won't work row by row, so you may keep your actual expression.

If you want something like passing selections from Date to SaleDates you can try with:

=Sum({<SalesDate=P(Date)>} Amount)

[wanyunyang]

Hi Ruben, thanks for replying. But by using Sum({<SalesDate=P(Date)>} Amount), every Date is showing total Amount. Is there a way to fix this?

And also, could you please explain more on "set analysis won't work row by row"?

[vishsaggi · Accepted solution]

Can your try like below: Make sure the date format in both Date and SalesDate should be similar.

= Sum(If(Date = SalesDate, Amount))

[wanyunyang]

This works. Thank you so much!

[vishsaggi]

No Problem.