I am trying to build one logic from excel to Qlik Sense Cloud. But after several efforts it's not working as expected. So need your help. I have attached test data. This has three data sheets for Aug'19.
1. Orders - these are orders created in one month.
2. Activities - these are activity closed in same month.
3. Orders_2mths - these are last two months orders data. It's a reference sheet for activities table. Activities pick IN_OUT & Country field from this sheet.
Now I am trying to build some charts based on above data. one of example is given in chart sheet. This is using Orders & Activities sheets only.
There will be two charts one for country & other for months. Country chart will show only latest month data and Months chart will show monthly trend and added month bars as year passes.
Now challenge is , if we connect Orders & Activities on basis of Order_No & Month. We lost few orders due to this mapping. If we add them only using Order_No, we didn't get monthly chart.
Country is available in both Orders & Activities. But we need to use order's Country_O for chart preparation as it's in denominator of ratio.