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.

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

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

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

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

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)

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

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

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