Count the degrowing value(negative value) by month

[Former Member]

Hi Experts,

      i want a formula for Count the degrowing value(negative value) by month.

so i used running Sum for both LY YTD Value and CY YTD Value,then i calculate growth %.

so i need a formula where i calculate all negative % and that to 04.2014 to till now.

Count should not consider 2013 values..

please help ;its urgent.

Thanks.

Ashwini

[Former Member]

Hi Ashwin,

If I got the scenario correctly, you are fetching one Month dimension and two measures i.e. LY YTD value and CY YTD value.

After this, you must be trying to calculate the growth something like

growth % = (TY YTD/LY TYD) -1          (%)

If LY value is higher then the growth % will be negative.

Please correct me if my understanding is incorrect.

If correct, I would like to know how many objects are present in the report block? Do we only have month and Growth % value or other dimensions as well as they will decide your calculation context.

If we only have Month, LY YTD, CY YTD and growth % ; then you can add another column and define it as:

[count] = IF ( [growth%] < 0 ; 1 ; 0)

Let me know if it helps.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

     correct and i already got but its showing result for both MM.2013 and MM.2014 because we are using LY Val.

i need a formula for sum of count that its only going to count 04.2014 to till now.

e.g;

currently i used this formula but i want the formula for bold letters

=Sum(If(Sign([Run Growth%])=-1;1;0))Where([Cal. year / month - Bex] InList("04.2014";"05.2014";"06.2014";"07.2014";"08.2014";"09.2014";"10.2014";"11.2014";"12.2014";"01.2015"))

here i hardcoded the year(its working) ,but i want the range formula for the above bold sentence...

i tried the with logic(its not working)

=Count(If(Sign([Run Growth%])=-1;1;0)Where([Cal. year / month - Bex]Between ("04.2014";"01.2015")))

please give the logic for above years or give any formula but its should not hard code value..

whenever i run the report it should takes 04.2014 to till(normally 04.2014 to 03.2015).

so if u understand above scenario then plz reply me ASAP.

Thanks In Advance.

Ashwin

[Former Member]

Hi Ashwin,

Please check if following logic works fine for you:

=Sum(If(Sign([Run Growth%])=-1;1;0))

Where

(

([Cal. year / month - Bex] > FormatDate( ("04."+FormatNumber(CurrentYear() -1) ; "mm.yyyy"))

And

([Cal. year / month - Bex] < FormatDate( ("03."+FormatNumber(CurrentYear()) ; "mm.yyyy"))

)

[Former Member]

Hi Yuvraj,

          how to get Current year ,please advice.

For your kind info,[Cal. year / month - Bex] format is coming MM.YYYY.

Thanks.

Ashwini

[Former Member]

Hi Ashwin,

Just a small change in the formula:

=Sum(If(Sign([Run Growth%])=-1;1;0))

Where

(

([Cal. year / month - Bex] > FormatDate( ("04."+FormatNumber(CurrentYear() -1) ; "MM.yyyy"))

And

([Cal. year / month - Bex] < FormatDate( ("03."+FormatNumber(CurrentYear()) ; "MM.yyyy"))

)

CurrentYear() is a default Webi function available in Webi itself.

Please parse this formula and check if you get any errors.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

Actually No such function is their like CurrentYear(), only CurrentDate() and CurrentTime();is their.

So plz help me in this regard..

Thanks.

Ashwin

[Former Member]

Hi Ashwin,

Sorry for the incorrect function. You can try to use the below formula:

=Sum(If(Sign([Run Growth%])=-1;1;0))

Where

(

([Cal. year / month - Bex] > FormatDate( ("04."+FormatNumber( Year(CurrentDate()) -1) ; "MM.yyyy"))

And

([Cal. year / month - Bex] < FormatDate( ("03."+FormatNumber( Year(CurrentDate()) ; "MM.yyyy"))

)

Regards,

Yuvraj

[Former Member]

Sorry Yuvraj,

   this formula is not working,when "Year(CurrentDate()) -1)",for prev year.

please suggest any alternate solution,becoz last time after i added this i asked....

Thanks.

Ashwin