Gross Profit %

Hello!

Looking for help with this. Our systems admin who helped create some of our reports built this saved search for me.

I am trying to make 4 new columns of info:

• Yesterday Est Margin %
• LY (last year) Est Margin %
• YTD Est Margin %
• YTD LY Est Margin %

I am unsuccessful.  Can anyone please help with how the formulas should be written? I attached a screenshot and highlighted the two formulas that my previous admin wrote.

Dan

Rookie Asked on January 7, 2020 in

I ran out of time to fully test these, but hopefully this gets you started:

Yesterday Est Margin %: (SUM(CASE WHEN {trandate}=({today}-1) THEN {estgrossprofit} ELSE 0 END))/NULLIF((SUM(CASE WHEN {trandate}=({today}-1) THEN {netamount} ELSE 0 END)),0)

LY (last year) Est Margin %: (SUM(CASE WHEN to_char({trandate},’YYYY’)=(to_char({today},’YYYY’)-1) THEN {estgrossprofit} ELSE 0 END))/NULLIF((SUM(CASE WHEN to_char({trandate},’YYYY’)=(to_char({today},’YYYY’)-1) THEN {netamount} ELSE 0 END)),0)

YTD Est Margin %: (SUM(CASE WHEN to_char({trandate},’YYYY’)=(to_char({today},’YYYY’)) THEN {estgrossprofit} ELSE 0 END))/NULLIF((SUM(CASE WHEN to_char({trandate},’YYYY’)=(to_char({today},’YYYY’)) THEN {netamount} ELSE 0 END)),0)

YTD LY Est Margin %: (SUM(CASE WHEN {trandate} BETWEEN to_date(‘1/1’||(to_char({today},’YYYY’)-1),’MM/DD/YYYY’) AND ({today}-365) THEN {estgrossprofit} ELSE 0 END))/NULLIF((SUM(CASE WHEN {trandate} BETWEEN to_date(‘1/1’||(to_char({today},’YYYY’)-1),’MM/DD/YYYY’) AND ({today}-365) THEN {netamount} ELSE 0 END)),0)

Note that on the last one I just did minus 365 days, which will lead to a one day inaccuracy on leap years.

Like I said, please run these and check them, I was not able to verify they are totally right