8 Replies Latest reply on Aug 27, 2016 5:48 PM by panjala Srinath

# Sum of quantity for all values which are not null

Hi,

I have a problem. I am trying to find the sum of all rows which are not null for each product no.

so for each product no. i want to list sum of all values where "Deal SO.Deal" field is not null. So, I will have two values - 1. sum of quantity for NULL value for each "Deal SO.Deal" and sum of the rest. Can i can some pointers on how to do this? Thanks in advance.

• ###### 1. Re: Sum of quantity for all values which are not null

Hey I see that you are using dealso.deal field from the secondary data source , can you please also attach the workbook with the question , that ll be more useful .I have a solution with table calculation but for that i ll be needing sample data set .

Thanks ,

• ###### 2. Re: Sum of quantity for all values which are not null

Hi Ajay,

The workbook is too huge to attach here. Can you provide any pointers towards the solution that you have in mind.

• ###### 3. Re: Sum of quantity for all values which are not null

Hi Prasenjeet,

I have a solution to your query using "Sets"
You can filter the Deal SO.Deal field and exclude the nulls.

After filtering the null fields, you can select the remaining ones to create a Set.

Now, when you display the In and Out members of this Set....and add Sum(quantity) to text, you will be able to see the required output.

For you reference, i am attaching the workbook with similar example,

Regards,
Kajal

• ###### 4. Re: Sum of quantity for all values which are not null

Hi Kajal,

Thanks for your effort. However, I dont think i have explained the problem properly. I have Deal no per product ID. So, a product id say 1234 might have many deal numbers including null. I want to see the sum of quantity of all non null and null deal no.s. For Example:

 product no Deal SO Qty Null Sum Sum of not nul A3R82AA 94069309 1,600 149 2,294 94012337 90 94011964 102 Null 149 94011904 188 94011079 9 94008162 252 93953502 27 93444117 26 A3D07AV 93302276 22 93260641 11 92990225 6 Null 15 15 39

I want an output similar to the above table. Help Please.

• ###### 5. Re: Sum of quantity for all values which are not null

You can achieve this in multiple ways:

Lets assume you've data set like this

Option 1 - Using calculated fields

For Null Qty you can create a calc field using below mentioned formula

IF ISNULL([DealSO.DealPriceID]) THEN [Qty] END

For Not Null Qty you can create a calc field using below mentioned formula

IF NOT ISNULL([DealSO.DealPriceID]) THEN [Qty] END

After creating calc fields just drag your Product and Measure Names in rows shelf and Measure Values on label.

So your out come will be like this

Option 2: Using Sets

Create a set on your DealSO.DealPriceID dimension. In condition tab just write below formula.

You can get a meaningful label instead of In/Out

Then you can drag your fields like this

Hope this help. Sample workbook (version 9.3) has been attached for your reference. Let me know If you've any query.

Mahfooj

2 of 2 people found this helpful
• ###### 6. Re: Sum of quantity for all values which are not null

Thanks a lot Mahfooj and Kajal for helping me out.

• ###### 7. Re: Sum of quantity for all values which are not null

Kindly close the thread by marking my answer as correct. So that It may help others who've similar query.

• ###### 8. Re: Sum of quantity for all values which are not null

The Best way to solve this is by creating two simple calculation

1.Qty with Null

2.Qty without Null

Formula 1.  IF ISNULL(Deal No) THEN Qty ELSE 0

Formula 2.  IF NOT ISNULL(Deal No) THEN Qty ELSE 0

Done! Use the calculation as required.

Thanks & Regards

Srinath. Panjala