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 .
The workbook is too huge to attach here. Can you provide any pointers towards the solution that you have in mind.
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,
Please let me know that if this was helpful.
F10.twbx 1.2 MB
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.
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.
Sample 9.3.twbx 12.1 KB
Thanks a lot Mahfooj and Kajal for helping me out.
Kindly close the thread by marking my answer as correct. So that It may help others who've similar query.
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