How to calculate total growth for current yerar over previous year

[Anonymous]

Hi i want to write an expression for total growth

expression for curremt year

=num(sum({< Sold_Year = {"$(=Max(Sold_Year)"}>} [inv value]/Sales_INR_Unit),'#,##0.0')

for previous year

=num(sum({< Sold_Year = {"$(=Max(Sold_Year)-1)"}>} [inv value]/Sales_INR_Unit),'#,##0.0')

[kaushiknsolanki]

Hi,

Just divide the Current Year expression by Previous Year Expression.

Regards,

Kaushik Solanki

[Anonymous]

but the data is coming blank

[prma7799]

Grwth :

(

(

(num(sum({< Sold_Year = {"$(=Max(Sold_Year)"}>} [inv value]/Sales_INR_Unit),'#,##0.0'))

-

(num(sum({< Sold_Year = {"$(=Max(Sold_Year)-1)"}>} [inv value]/Sales_INR_Unit),'#,##0.0')))

/

(=num(sum({< Sold_Year = {"$(=Max(Sold_Year)-1)"}>} [inv value]/Sales_INR_Unit),'#,##0.0'))

) *100

Or

((Column(1)-column(2))/(column(2)))*100

[kaushiknsolanki]

Hi,

Check of individual expression gives you proper result.

Check if anyone of the expression is 0 or not.

Regards,

Kaushik Solanki

[prma7799]

you have to use aggr function fot Sales_INR_Unit

or

sum(Sales_INR_Unit) or avg(Sales_INR_Unit)

[Anonymous]

here its giving an error

[prma7799]

check this

(((num(sum({< Sold_Year = {"$(=Max(Sold_Year)"}>} [inv value]/Sales_INR_Unit),'#,##0.0'))

-

(num(sum({< Sold_Year = {"$(=Max(Sold_Year)-1)"}>} [inv value]/Sales_INR_Unit),'#,##0.0')))

/((num(sum({< Sold_Year = {"$(=Max(Sold_Year)-1)"}>} [inv value]/Sales_INR_Unit),'#,##0.0'))))*100

[jonathandienst]

I assume that this current year expression works correctly:

Sum({<Sold_Year = {"$(=Max(Sold_Year))"}>} [inv value] / Sales_INR_Unit)

Then this is a valid expression for previous year:

Sum({<Sold_Year = {"$(=Max(Sold_Year) - 1)"}>} [inv value] / Sales_INR_Unit)

If the prior year does not work, you may need to override one or more date selection fields as they will conflict with Max(Sold_Year) - 1. Something like

Sum({<Sold_Year = {"$(=Max(Sold_Year) - 1)"}, Month, Year>} [inv value] / Sales_INR_Unit)

Replace the bold field names with the correct field names that are being used to make date selections.

[jonathandienst]

Don't embed these Num() inside an expression. They are only formatting functions which do absolutely nothing inside the expression apart from adding clutter.

If you really need the formatting, apply it to the final result only:

Num(((sum({<Sold_Year = {"$(=Max(Sold_Year))"}>} [inv value] / Sales_INR_Unit) -

sum({<Sold_Year = {"$(=Max(Sold_Year) - 1)"}>} [inv value] / Sales_INR_Unit)) /

Sum({<Sold_Year = {"$(=Max(Sold_Year) - 1)"}>} [inv value] / Sales_INR_Unit) * 100), '#,##0.0')