Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
EdgarMM
Contributor III
Contributor III

Smoothing BackOrder - Set Analysis - Aggr

Hi Gurus

 

            I hope you can help me with this requirement; I create two pivot tables, the first one is showing the backorder per product and sales channel, the second pivot table is showing the over sales per product and sales channel as well; The information I have in these two tables is

 

BACKORDER

 

Product

Sales Channel

Ammount

Product B

Channel 2

117

Product D

Channel 3

100

  

217

 

 

OVER SALES

 

Product

Sales Channel

Ammount

Product A

Channel 4

7

Product B

Channel 2

97

Product D

Channel 3

83

Product C

Channel 1

5

  

192

 

            The table I’m looking for should be something like this

 

EXPECTED TABLE

 

Product

Sales Channel

Ammount

Product B

Channel 2

20

Product D

Channel 3

17

  

37

 

            The information will change per month and year based on user’s selection, knowing that, I cannot set sales channel as fixed in the third table:

 

            The script for the first table is:

 

                       Aggr(sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {'11', '15'}>}
                (IF($(vDocuments), (Domestic_Amount_MXP)))), Sales_Channel  )

 

            The Script for the second table is:

 

sum(aggr(
IF((
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {' ' , '11', '15'}>}
   (IF($(vDocuments), Domestic_Amount_MXP))))
   /
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Gross_Amount_MXP))
   )>1,

   (
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source = {'ERP'}, Sales_Order_Item_Rejection_Reason = {' ' , '11', '15'}>}
   (IF($(vDocuments), Domestic_Amount_MXP))))
   -
   (sum({$<Sales_Order_Year = {$(#=Only(Sales_Order_Year))}, [Budget]={'Yes'}, Source={'$(=$(Versus))'}>} Gross_Amount_MXP))
   )
  
,0
), Product, Sales_Channel)
)

 

          The information from these two tables is being shown in a waterfall graph

 

            The first bar is showing 217 (following the example of the first table related to BackOrder), the second bar is showing 192 (related to the second table for Over Sales) and the third bar should be showing 37 since we are looking the difference between these two numbers only for those cases in which the products and the sales channel coincide.

 

            I will appreciate your help on this.

 

Regards,

Edgar.

3 Replies
sunny_talwar

Did you try using Expression 1 - Expression 2 for difference?

EdgarMM
Contributor III
Contributor III
Author

Hi Sunny

 

     I tried using that expression but in my example the reusult was 25 (217 - 192 =  25), and what I'm looking for is using Expr 1 - Expr 2 but only for those products and sales channels that exists in both pivot tables, in my example should be product B and D in combination with Sales Channel 2 and 3, in that case the result should be 217 - 180 = 37.

 

Regards,

Edgar.

Brett_Bleess
Support (Former)
Support (Former)

The only idea that comes to mind here is to use some sort of flag field that could be used in the expressions to get the 'matched' values of which you spoke ignoring those that do not match, best idea that popped into my head, maybe someone else will be able to expand upon it for you.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.