[Cloud]

Hi,

I need some help, I’m trying to create a search to show me Break down for 2015/2016 the ‘Percentage of Revenue we did in each category’.

I can’t remember how to do this, I’ve done it back 5 years ago and I can’t find any of my calculations.

So Far I’ve Grouped the Category (No Hierarchy) and Summed (Amount – Transaction Total). Don’t know if this is right? Am I correct to say it should be Amount – Gross?

Thanks
This is a cached copy. Click here to see the original post.

[mchen]

amount – transaction total is for main line.

what is category? if this field is a body field, then you are okay. you just need main line = yes.

[Cloud]

Category is Site Category. So want to show the Percentage of Revenue for Categories like – 2 Wheel Rollators, 3 Wheel Rollators, Bed Rails, Bed Sheets etc.

When you say Main Line, what do you mean?

Also how can I calculate Percentage to show me in two different periods? Something like IF Case is {Date Created} within 01/01/2015 to 12/31/2015 then [PERCENTAGE] else NULL end. That’s how I see it?

Can you advise?

Thanks

[mchen]

are you talking about percentage of total or percentage increase between two periods.

[Cloud]

Initially it was only Percentage of Totals but I would also like the percentage increase between two periods please. Thanks

[mchen]

Criteria

Account = Sales Revenue

Posting = True

There is a function column next to summary type.

Category

Sum of Amount

Sum of Amount (Function: % of Total)

to sum specific date range

Sum of Formula(Numeric) CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END) this will cover year 2015

Sum of Formula(Numeric) (Function: % of total) CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END) this will cover year 2015

[Cloud]

Ah Brilliant. So the 2nd column of Sum of Amount will get me percentage.

Could you help me further with the percentage increase between 2015 to 2016?

I see it as –

CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END) + (ADD) CASE WHEN to_char({trandate},’YYYY’) = ‘2016’ THEN {amount} ELSE 0 END) / (Divide) CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END)

[mchen]

you have LY vs YTD

or do you want to compare LYTD vs YTD.

LY + YTD / LY?? (This is what you have) this will also give an error if LY = 0.

* to prevent an error use LY + YTD / nullif(LY,0)

(LY + YTD) / nullif(LY,0) ??

or

YTD / nullif(LY,0) – 1 ??

or

YTD / nullif(LYTD,0) – 1 ?? I usually use this to compare YTD vs LYTD increase in percentage

[Cloud]

Hi,

Sorry Mchen, I’ve been off on leave and just got back.

1. To answer you in your last Question – I just want to show the increase/decrease from 2015 vs 2016 – Say Bed Pans decreased by 10% in 2016. Actually sold more in 2015. but going off the last formula this will give me the formula I need

CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END) + (ADD) CASE WHEN to_char({trandate},’YYYY’) = ‘2016’ THEN {amount} ELSE 0 END) / (Divide) NULLIF(CASE WHEN to_char({trandate},’YYYY’) = ‘2015’ THEN {amount} ELSE 0 END),0)-1

Is this correct?

Going back to

2. The criteria you gave me – I couldn’t find ‘Sales Revenue’ under Account. Can I just use Sales Order?

3. The Results you gave me – Category isn’t found under Transaction Search, So I used Item Fields – Category (no heirarchy).

3b. I can’t use a formula under Amount, so I guess it would be Formula Numeric but that didn’t work and it gave me invalid expression maybe because I didn’t use the Account Sales Revenue, however I selected Account Sales order but it showed up empty

[mchen]

look under your item inventory and see where your income gets posted.

there are probably others way of doing this, but this is what i use.

screenshot below.

for % Chg. use max summary. SUM(2016 sales) / NULLIF( SUM(2015 sales), 0) – 1

[Author]

Posts