RE: 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.
Thank you in advance!
Dan
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
OlivierGagnon, thank you so much! I will try these as soon as I can come up for some air. 🙂
