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

Gross Profit %

Rookie Asked on January 7, 2020 in Accounting.
Add Comment
1 Answer(s)

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

Advanced Answered on January 10, 2020.

OlivierGagnon, thank you so much! I will try these as soon as I can come up for some air.  🙂

on January 13, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor