Discussion board where members can learn more about Qlik Sense App Development and Usage.
I have a pivot table which I would like users to drill down on rows as follows:
The number they will be viewing is a formula, based on other formulas. The resulting number for this formula seems to change as I drill down. Here is the formula for Total Profit/Loss:
Net Proceeds- ValueHarvested.
Net Proceeds is defined as Sum(GrossProceeds) - Sum(GrossCost)
ValueHarvested is defined as (Sum(Acres) * Avg(CostPerAcre))
The final expression looks like this:
sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)
I've done some digging around in the forums and I can't quite figure out what might be causing the numbers to change as I drill down. Could it be that I need to use Aggr? Perhaps I need to use Dimensionality? Any help would be appreciated. Thanks.
Here's what the numbers look like before I drill down to level 3:
When I drill down to level 3, it changes to this:
Other numbers change too, not just the Total Profit/Loss number that I mentioned above.
I created a separate "test" pivot table, that includes fewer measures. In the test pivot table, everything works fine:
I think it has something to do with the other measures in the "broken" pivot table; either there are too many (11), or one or more of them is causing my expression to get "confused".
Any ideas or suggestions?
Check the expression FldOnBgt%, you are using a key field: "WorkOrder", you should create another field with this, called "WorkOrderCount" to be able to use it in the expression: Sum (FldOnBgtFlag) / Count (DISTINCT WorkOrderCount)
I have had a similar problem.
What I did is generate a table with all the necessary data for the formula and then, taking your example, I used the following formula:
avg(GrossProceeds - GrossCost - (Acres * CostPerAcre))
If this does not work could you upload sample data to be able to test?
PD: sorry, my english is poor. I hope you understand.
You can try this expression too.
Sum(Aggr((sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)),
I think I see what you are doing...but I don't think it is working when I do the same thing in my application. I added the following to the data load editor after the Budget table is built:
Avg(CostPerAcre) as CostPerAcreB
Group By RanchLotWOKey;
I changed my variable to point to the new CostPerAcreB measure, but the numbers are too high.
I've attached an updated copy; if you have time to look I would appreciate it.