6 Replies Latest reply on May 14, 2018 12:01 PM by Deepak Rai

Calculating Count of Orders for Only 2 Ship mode

Hi Folks,

I have a situation which i can emulate with superstore data set. I would like to show count of all orders and right next to it i would to show the count of orders for only for 2 ship mode for e.g. Calculate orders only for  "Second Class" and "Standard Class". I tried using LOD but not been able to come up with the correct formula.

• 1. Re: Calculating Count of Orders for Only 2 Ship mode

{FIXED:COUNT(IF Ship mode="Second Class" OR Ship mode="Standard Class" THEN Order ID END)}

This would give you Count for entire dataset.

If you need per Customer, Then

{FIXED [Customer Name]:COUNT(IF Ship mode="Second Class" OR Ship mode="Standard Class" THEN Order ID END)}

• 2. Re: Calculating Count of Orders for Only 2 Ship mode

Deepak Rai Thanks for Sharing this, this is working. However i am wondering when i use this LOD field in the view the aggregation changes to SUM and i wonder why ? Because its doing a count inside. The reason why i am clarifying this is because, could this cause any issue while i will work through it as it evolves ?

?

• 3. Re: Calculating Count of Orders for Only 2 Ship mode

Convert Sum to Count. Right Click, Go to Measure, Select Count..Won't affect anything

• 4. Re: Calculating Count of Orders for Only 2 Ship mode

Hi Deepak,

Just tried the solution you gave on the real dataset and for some reason its not working. Below is the formula i used.

"Split By Value" is a parameter

Bar 2 Calc

Bar 3 calc

On the left (the first bar graph) is the total cases, so the both on the right after using the formulas above should have lesser number since its filtering the statuses i mentioned.

Once i change the sum() to Count() this happenes

• 5. Re: Calculating Count of Orders for Only 2 Ship mode

Hey Deepak Rai figured it, i had to use INCLUDE instead of  FIXED and its giving me the correct numbers now, however changing from SUM() to COUNT() is returning what you see above

• 6. Re: Calculating Count of Orders for Only 2 Ship mode