Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

Pivot table numbers change when I drill down on rows

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:

Pivot numbers change 1.jpg

When I drill down to level 3, it changes to this:

Pivot numbers change 2.jpg

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:

Pivot test numbers work.jpg

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!

1 Solution

Accepted Solutions
spividori
Specialist
Specialist

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.

View solution in original post

20 Replies
spividori
Specialist
Specialist

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.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
mikegrattan
Creator III
Creator III
Author

Same behavior, unfortunately.

mikegrattan
Creator III
Creator III
Author

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

mikegrattan
Creator III
Creator III
Author

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

Thanks.

mikegrattan
Creator III
Creator III
Author

Sample data attached.

spividori
Specialist
Specialist

Hi.

See the attached example.

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

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

Regards.

mikegrattan
Creator III
Creator III
Author

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.

spividori
Specialist
Specialist

Hi.

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

Regards.