How can I stop QlikView to summation my numbers?

[Anonymous]

Hi Qlikview Community

I have an easy problem but I can't figure it out.

I can't stop QlikView to summation my numbers even though I don't have the sum expression

I have the database in the excel sheet and I want QlikView to show my exactly the same as in the database.

But when you have Revenues that are 100 and -100 then Qlikview gives 0 but I want Qlikview to show 100 and -100.

Can anybody help me?

[Colin-Albert]

Perhaps you could load the positive values into a Credit field, and the negative values into a Debit field. Then you could sum the Credits and Debits separately.

load

     If(Revenue >= 0, Revenue as Credit,

     If(Revenue <= 0, Revenue as Debit,

     Revenue,

...

[rwunderlich · Accepted solution]

The Summation is being done because you are using the sum() function in your expression. If you want to display every row, add Charge Code and Revenues as Dimensions and uncheck "Suppress zero values" on the presentation pane.

That will work as long as you don't have a duplicate for Jobref/ChargeCode/Revenues. To be able to display separate rows for the duplicates, add a unique identifier into your load statement.

LOAD [Job Ref],

rowno() as RecId,

     [Charge Code],

     Revenues

FROM

tilraun.xlsx

(ooxml, embedded labels);

And then use RecId as a Dimension in your chart. You can choose to hide the RecId column and still get the benefit of the uniqueness. See Qlikview Notes: Using Column Visibility

-Rob

http://masterssummit.com

http://robwunderlich.com