Notifications
Clear all
Analytica Q&A
2
Posts
2
Users
0
Reactions
1,322
Views
May 16, 2022 6:22 pm
Hi, i was needing help with setting up some mapping based on criteria and then using that mapping to carry out some addition/summing between these items. Below is what i am trying to achieve.
I have 2 indexes - "All" & "Months" and a variable - "Current_Vals" (indexed by "All" and "Months") like so:
| Index: All | Index: Months | Â | Var: Current_Vals | Â | Â | Â |
| Â | Â | Â | Â | 1-Jan-22 | 1-Feb-22 | 1-Mar-22 |
| Prod_A | 1-Jan-22 | Â | Prod_A | 0 | 1 | 1 |
| Prod_A_Overseas | 1-Feb-22 | Â | Prod_A_Overseas | 0 | 2 | 2 |
| Prod_B | 1-Mar-22 | Â | Prod_B | 1 | 2 | 3 |
| Prod_B_Overseas | Â | Â | Prod_B_Overseas | 0 | 0 | 0 |
| Prod_C | Â | Â | Prod_C | 6 | 8 | 10 |
| Prod_C_Overseas | Â | Â | Prod_C_Overseas | 2 | 1 | 0 |
| Prod_D | Â | Â | Prod_D | 5 | 5 | 5 |
| Prod_D_Overseas | Â | Â | Prod_D_Overseas | 2 | 2 | 0 |
With the mapping, i want to carry out a summation on the "Current_Vals" like so:
- Prod_A = Prod_A + Prod_A_Overseas
- Prod_B = Prod_B + Prod_B_Overseas
- etc
but not this:
- Prod_A_Overseas = Prod_A + Prod_A_Overseas
- Prod_B_Overseas = Prod_B + Prod_B_Overseas
So the totalling results like so (interim step):
| Var: Interim_Step | Â | Â | Â |
| Â | 1-Jan-22 | 1-Feb-22 | 1-Mar-22 |
| Prod_A | 0 | 3 | 3 |
| Prod_A_Overseas | 0 | 2 | 2 |
| Prod_B | 1 | 2 | 3 |
| Prod_B_Overseas | 0 | 0 | 0 |
| Prod_C | 8 | 9 | 10 |
| Prod_C_Overseas | 2 | 1 | 0 |
| Prod_D | 7 | 7 | 5 |
| Prod_D_Overseas | 2 | 2 | 0 |
but my final output is this:
| Var: Final Output | Â | Â | Â |
| Â | 1-Jan-22 | 1-Feb-22 | 1-Mar-22 |
| Prod_A | 0 | 3 | 3 |
| Prod_B | 1 | 2 | 3 |
| Prod_C | 8 | 9 | 10 |
| Prod_D | 7 | 7 | 5 |
The indexing here can/is different (but a subset of "All").
Any help is greatly appreciated. Thanks
Â
Â
1 Reply
Jun 10, 2022 7:21 am
This looks like you have three dimensions, i.e.,Â
Â
Index Product := ['Prod_A', 'Prod_B', 'Prod_C', 'Prod_d'] Index IsOverseas := ['Not Overseas', 'Overseas'] Index Month := 1-Jan-2022 .. 1-Mar-2022
If you organize your data that way, then you can doÂ
Sum( Current_Vals, IsOverseas )
for your Final_output corresponding to
- Prod_A = Prod_A + Prod_A_Overseas
- Prod_B = Prod_B + Prod_B_Overseas
- etc
Â
