Need advise with inputsum/avg fields. I am attaching a QV application demo I created for this question. It has 6 columns: SKU, Net Volume, Gross Revenue, GR Sum, ASP, ASP Display. My ASP field is an inputavg() field. This is what pricing team wants to play with to see the impact on the Gross Revenue and other finance measures down the stream. Gross Revenue is a calc field: Net Volume * ASP. Gross Revenue field has "expression total" option and GR Sum, has SUM in "Total Mode" in table property. Values are different. My problem is that I want my ASP total to be calculated using GR SUM/Net Volume but when I put "expression total" in ASP field it calculates ASP using "expression total"mode for Gross Revenue. You will see it from the example. I tried to trick it by creating ASP Display field but it did not help. I tried using aggr() function as was suggested by one of QV guys during Q-Day but it did not work. Any advise? is it a missing functionality in QV if so, how I can submit it for the future enhancements for the tool? p.s. for some reason when I try to attach *.qvw file the site tells me that this is a not allowed type. I changed extension to .txt[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7142.inputfield.txt:550:0]
I've made some progress on calculating the correct Total ASP using AGGR *********************** (AGGR(SUM(BudgetInput), [Product])*AGGR(AVG(BudgetInput_ASP), [Product]))/ AGGR( SUM(TOTAL BudgetInput), [Product]) Now, I have in one column (ASP) correct ASPs by row and in the second column (last column in pic), I have correct ASP for total. Any ideas on how to merge them togeter?