Calculate Margin %, Discount % change, Sales change and Sales change %

[tas]

Hi, I have to express a formula for last year to date of margin %,margin % change, discount %, discount % change, sales change and sales change %

Currently this is my formula for sales lytd: Sum({$<[OrderDate.autoCalendar.InYTD]={1},[OrderDate.autoCalendar.YearsAgo]={1}>} Sales)

My formulas for margin % and discount % is only applicable for current year to date

[Or]

Your formula looks to be correct for sales, and you haven't posted the formulas for anything else, so what exactly do you need help with? Normally you'd just use the same set analysis but with whatever formula you use to calculate margin %, discount %, etc. e.g.

Sum({$<[OrderDate.autoCalendar.InYTD]={1},[OrderDate.autoCalendar.YearsAgo]={1}>} Margin)

/

Sum({$<[OrderDate.autoCalendar.InYTD]={1},[OrderDate.autoCalendar.YearsAgo]={1}>} Sales)

[tas]

My formulas are:

Discount %: (Sum(CataloguePrice*Quantity)-Sum(Sales))/ sum(CataloguePrice*Quantity) This is only for current year where as I need last year

Using the last year discount %, I can then calculate the discount % change 

Margin %:Sum(Sales-(UnitCost*Quantity))/Sum(Sales) Again this is for current year but I need last year 

Using the last year discount %, I can then calculate the margin % change

Sales last year to date: Sum({$<[OrderDate.autoCalendar.InYTD]={1},[OrderDate.autoCalendar.YearsAgo]={1}>} Sales)

I would have to find the sales change and so far this formula is only giving me 0, the formula is: (Sum({<Year = {2021}>} Sales) - Sum({<Year = {2020}>} Sales)) / Sum({<Year = {2020}>} Sales)

[Or]

What fields are being selected, if any? If you have selections on certain fields, that could in interfere with your formulas. Likewise, chart dimensions may also impact your expressions. 

There doesn't seem to be anything obviously wrong with those formulas (other than the fact that they're full year, not YTD). You could split the two parts and check each individually see what values, if any, are being returned. For the LYTD, you can use formulas similar to what I already posted which is just your existing set analysis from Sales repeated over as many sum() formulas as needed.