topic Re: Pivot table numbers change when I drill down on rows in App Development

Pivot table numbers change when I drill down on rows

mikegrattan — Wed, 19 Apr 2017 22:17:02 GMT

I have a pivot table which I would like users to drill down on rows as follows:

Location

Grower

Ranch

Commodity

Work Order

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?

Thanks!

Re: Pivot table numbers change when I drill down on rows

spividori — Thu, 20 Apr 2017 12:29:31 GMT

Hi.

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?

Regards.

PD: sorry, my english is poor. I hope you understand.

Re: Pivot table numbers change when I drill down on rows

kaushiknsolanki — Thu, 20 Apr 2017 12:42:50 GMT

You can try this expression too.

Sum(Aggr((sum(GrossProceeds) - sum(GrossCost)) - (Sum(Acres) * Avg(CostPerAcre)),

Location,Grower,Ranch,Commodity,Work Order))

Regards,

Kaushik Solanki

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Thu, 20 Apr 2017 16:43:21 GMT

Same behavior, unfortunately.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Thu, 20 Apr 2017 16:45:36 GMT

Same result. I'll go ahead and upload a copy of the application separately.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Thu, 20 Apr 2017 16:51:21 GMT

Here's a copy of the application if anyone would like to poke around.

Thanks.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Thu, 20 Apr 2017 17:05:41 GMT

Sample data attached.

Re: Pivot table numbers change when I drill down on rows

spividori — Thu, 20 Apr 2017 20:48:11 GMT

Hi.

See the attached example.

I make a "Binary" to the original .qvf.

I worked on the "Tot Prof / Loss" column.

Regards.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Fri, 21 Apr 2017 15:12:23 GMT

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:

Left Join(Budget)

Load RanchLotWOKey,

Avg(CostPerAcre) as CostPerAcreB

Resident WorkOrders

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.

Thanks.

Re: Pivot table numbers change when I drill down on rows

spividori — Fri, 21 Apr 2017 20:28:57 GMT

Hi.

Check the column "NetProceeds", it has value 0 or null and in the previous example had values.

Regards.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Fri, 21 Apr 2017 20:36:43 GMT

Understood; I caught that after I posted the last qvf. I reloaded the data and the Net Proceeds are now there, but the behavior is still the same. As soon as I drill down to Location -> Grower -> Ranch -> and expand a ranch, the number changes.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Fri, 21 Apr 2017 20:38:19 GMT

Update qvf attached.

Re: Pivot table numbers change when I drill down on rows

spividori — Fri, 21 Apr 2017 21:33:26 GMT

Hi.

Check it out now. It should work. It works for me.

Regards.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Fri, 21 Apr 2017 21:39:15 GMT

What changed? It does seem to be working now.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Fri, 21 Apr 2017 21:48:42 GMT

Ooops, spoke too soon. I'm still having an issue when I add more columns to Measures. Several measures are behaving this way.

Re: Pivot table numbers change when I drill down on rows

spividori — Fri, 21 Apr 2017 22:16:44 GMT

Hi.

It does not open the file, it is corrupt.

But you should try to do what we did to calculate: "Tot Prof / Loss" for the other measures.

Regards.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Mon, 24 Apr 2017 13:35:11 GMT

Here's another copy.

I don't believe the solution worked for the Tot Prof / Loss measure. As soon as I added other measures to the pivot table the behavior for that measure, as well as other measures, begins to happen again.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Mon, 24 Apr 2017 13:37:51 GMT

Attaching again. Last attachment didn't seem to work.

Re: Pivot table numbers change when I drill down on rows

spividori — Mon, 24 Apr 2017 20:45:42 GMT

Hi.

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)

Regards.

Re: Pivot table numbers change when I drill down on rows

mikegrattan — Mon, 24 Apr 2017 21:12:06 GMT

That did the trick! Perhaps Qlik was counting workorders from the foreign key table as well? Strange that the numbers were good for Location/Grower/Ranch but started to go crazy at the next level of drill-down.

Isolating the WorkOrders as a separate field in one table as WorkOrderCount, and then basing the formula off that measure was the key to solving the issue.

Thank you very much!