Cost comparison to LY

[johngouws]

I have an existing Sales app and I would like to compare the cost prices of the items I sold this year to the same items sold the previous year.

Currently I have this expressions to calculate this ytd values:

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost])

and SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) to calculate previous ytd.

I would like to only show the costs for item sold this years as well as last year and exclude items that were sold in only one of the two years.

I am not entirely sure if this can be done but would appreciate any help in this regard.

Thanking you.

[andrey_krylov · Accepted solution]

Hi, John, try to multiply by the condition of the fact of sales in a related year

your first expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost])  * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) , [You Item Field]) > 0, 1)

second expr

SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),-1))<=$(=AddYears(Today(),-1))"} >} [Unit Cost]) * If( Aggr(SUM({<[Sales_Fin_Year]=,Sales_Month=,Date = {">=$(=YearStart(Today(),0))<=$(=AddYears(Today(),0))"} >} [Unit Cost]), [You Item Field]) > 0, 1)

[johngouws]

Hi, Andrey. thank you very much for this solution. It is exactly what I was looking for.