COUNT Based on Condition of another Variable

[Former Member]

Good Day Folks,

I was wondering if this is possible in Webi.  I have a data set from which I would like to make the following Deductions:

Data Set is below:

Any suggestion would be appreciated.

Thanks Alot.

[mhmohammed · Accepted solution]

Hi Ademola,

Try using an If Then Else statement to achieve this. What numbers does Average Mape show, is it 0.00 to 1.00 and you format it to show as Percent in the report? Lets start with the below formula and see what happens.

=If([Average Mape] In ([Product]) < 0.10) Then "Good"

ElseIf([Average Mape] In ([Product]) between (0.11;0.20)) Then "Bad"

ElseIf([Average Mape] In ([Product]) between (0.21;0.40)) Then "Ugly"

ElseIf([Average Mape] In ([Product]) > 0.40) Then "Terrible"

Let us know how this looks and what is it missing.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Thanks Mahboob,

what I provided was a sample data set. Here's what the real data looked like after inserting the formula you provided.

[mhmohammed · Accepted solution]

Hi Ademola,

Seems that the Average Mape value is 0 (Zero) where you got those #DIV/0 error.

Question: How are you getting Average Mape? I think you created a variable in the report as =[Mape]/Sum([Mape]). If Yes, then update that variable by including an If Then Else statement as = If(IsError([Mape]/Sum([Mape]))=1) Then 0 Else [Mape]/Sum([Mape])

Does that make sense? Now the Average Mape will be 0 in such cases. Let us know if this helps.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

The only problem with that is that it will show as 0% error, which is not a true reflection of what is happening. I would rather have the report completely ignore such lines with errors. Is this possible?

[Former Member · Accepted solution]

[mhmohammed · Accepted solution]

Hi Ademola,

Create a filter on the table with Average Mape object as Average Mape Not Equal to 0 (or 0% Error) and see what happens.

Thanks,
Mahboob Mohammed

[Former Member · Accepted solution]

That worked. Thanks. So going back to my original question how do i get a table like this?

[mhmohammed · Accepted solution]

Hi Ademola,

You already created Grade variable, create a variable for WeekNumber with the formula =Week([Date]) and one more variable as ProductCount as =Count([Product]) in ([Grade]).

Do you really want to show the text as "Count of Product with Average Mape < 11%' you want to show the count in numbers?

If you want to show as text, you can create a variable as

=If(Grade=Good) Then "Counf of Product with Average Mape< 11%"

Elseif....

If you want to show the actual count of products in number, please attach a sample image.

Let us know.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Thanks alot Mahboob,

Looks like we are very close now. the only issue though is that the count value is not accurate: I dont have this many products. The "Good" alone is more than all of my products which 1023 in total

[mhmohammed · Accepted solution]

Hi Ademola,

Can you try to update the Product Count as =Count([Product];Distinct) in ([Grade]) and check what happens?

Thanks,

Mahboob Mohammed

[Former Member]

hi,

We have answered similar question today.

Refer http://scn.sap.com/thread/3885490

Create a dimension variable Grade: If(averagemape<10;";"Good";if(averagemape>10 and averagemape<20;"Bad";if(averagemape>20 and averagemape<40;"Ugly";"Terrible")))

create second variable as count(product)

here my question is why do you want to count multiple times? meaning you want to compare the averages in overlapping ranges. A product which falls in <11 category may also fall in 10 & 21 category.

It means you are counting the product multiple times. I have given example with normal range like <10, 10-20 etc

If you want the range as mentioned by you then please change the formula and test how it behaves in overlapping scenario.

As per my knowledge if a record is counted as part if <11 group then it will not come in 10 to 21 group. So provide clear requirement in this scenario with sample output

[Former Member]

Forgive my method, that always used to work for me on a different tool.

[jyothirmayee_s]

Hi,

Yes you can,

Create a variable in WebI

@Grade

=if([Average Mape])<11% then "Good"

Else if...

..

Drag and drop the variable into the report.

Attach data set in excel or let me know how it goes with variable?

Also post screen shot of the result set after the variable insertion.

Thanks,

Jothi

[Former Member]

Thanks Jothi

what I provided was a sample data set. Here's what the real data looked like after inserting the formula you provided.