topic Re: Find a Difference between Two Totals, Show as 'True' or 'False' in QlikView

Find a Difference between Two Totals, Show as 'True' or 'False'

dinicholls — Wed, 08 Apr 2015 15:31:58 GMT

Hi All,

I'm hoping someone might be able to shed some light on a problem for me? I can't for the life of me see how this can be done in QV, but know you must be able too!

I currently load a lot of month end files into a report. It sums the overall totals, and groups them according to the file type.

What I need it to flag, is if there is a difference between the summed total of 3 different file types within one group.

I can't unfortunately post anything because of IT being company information, and the policy they have here.

I have attached a copy of what the excel version of this file does, including the formula.

This is how I need it to be replicated in my QV report.

Is this possible?

Thanks

Re: Find a Difference between Two Totals, Show as 'True' or 'False'

stigchel — Wed, 08 Apr 2015 16:52:56 GMT

You can use the chart inter record function, something like

if(Top(Sum(Value1),1)=Top(Sum(Value1),2) and Top(Sum(Value1),1)=Top(Sum(Value1),3),'True','False')

Also see attached

Re: Find a Difference between Two Totals, Show as 'True' or 'False'

dinicholls — Thu, 09 Apr 2015 13:15:58 GMT

Hi,

How can I apply that to my pivot table? Example of it attached.

I need to replace the 'Total's', under the 'File Type' groups, with the True / False statement.

I have a total of 126 of these that I need to write to cover all the possibilities.

Thanks

Re: Find a Difference between Two Totals, Show as 'True' or 'False'

stigchel — Thu, 09 Apr 2015 13:35:36 GMT

About the same as what I posted, the only thing is that you have an extra dimension. That changes the Dimensionality() value of where you want your true/false flagging. You can check by adding an expression to the pivot table with

=Dimensionality()

To check

I'm not sure what you mean by the 126, and cover what possibilities...

Example of pivot table is attached

Re: Find a Difference between Two Totals, Show as 'True' or 'False'

dinicholls — Thu, 09 Apr 2015 14:47:12 GMT

Argh!!!

I appear to be doing something wrong, as I'm just getting figures?!

I've copied over your formula, but am I missing something? Have you used something somewhere I don't know about?! I've had a nosey through, and can't see anything obvious.

Thanks

Re: Find a Difference between Two Totals, Show as 'True' or 'False'

stigchel — Thu, 09 Apr 2015 14:53:34 GMT

I think that in your pivot table the Dimensionality() is not 1 where you want the True/False flag. Like said, add an extra expression

=Dimensionality()

Check the results and adjust the if so the flag is calculated in the correct place.

Otherwise please provide a sample app