How to display only 1 value in my column in my simple table report and show the other 2 as 0?

[jctorres]

Good afternoon, dear friends.

I'm having a problem applying the sum(TAX_REMAINING) measure in a simple table report. It shows the same value three times, when it should only display one value and the other two values ​​are zero. This is a native error from the database.

Here's an example of how my report is displayed:

So, in the tax code field it shows 3 details and of those it should only show 1 value as shown in the following image:

Therefore, the report already shows me that and I am applying the following measure:

If(not IsNull(aggr(SUBTOTAL_LINEA, NUMERO_TRANSACCION)),SUM(SUBTOTAL_LINEA),0)

But when I select another branch and the transaction number, in my subtotal field it shows me all 0, when it should show me the same different values ​​as they appear in the last column Subtotal MXN, so I don't know how to apply my measure.

Please, I need to know how to apply the formula so I can visualize the data.

I appreciate your support.

I look forward to your comments.

[rubenmarin1]

Hi, that looks like an issue with the data model, the TAX_CODE field is not related to the line of the TAX_REMAINING, each value of TAX_CODE has the relation to the same value of TAX_REMAINING, so you will need to check the relations between those fields on the data model.

[henrikalmen]

So you have a knows problem in your database, and in the second image it seems you have done some kind of workaround that is functional for you. How did you achieve that; what is the expression that calculates [Sub Total] in your second image? Did you do it in frontend or in loadscript? 

What is the selection you have made in image 3, where you are not getting the [Sub Total] column as you want it? What are the fields SUBTOTAL_LINEA (is that the backend name for [Sub Total]?) and NUMERO_TRANSACCION used inside the aggr function? And it seems you already have the result you need in [Sub Total MXN]..?

[jctorres]

Hello Henrikalmen, if the database contains information repeated 3 times when it should be displayed once:

The first image of the simple table shows how it is repeated from the database. Then I applied the following measure:
If(not IsNull(aggr(SUBTOTAL_LINEA, NUMERO_TRANSACCION)), SUM(SUBTOTAL_LINEA), 0)
where this formula shows only 1 value. This is how I want it to be displayed in my table and it works correctly. The second image shows the Subtotal field. There are branches that show three types of tax codes (AC TAX IVAR, RET PF 10 RATE, and RET PF RATE). However, there are other branches that show the same tax code, but the Subtotal amount is different. An example is the Subtotal MXN field, which is marked in the green box in the third image. The difference is that the Subtotal field does not apply the exchange rate in USD and MXN, and the Subtotal field MXN applies the currency exchange rate to MXN. So, the Subtotal field should show the same values ​​as the MXN Subtotal field. The problem is that the Subtotal field shows zeros. Applying the following measure: If(not IsNull(aggr(SUBTOTAL_LINEA, NUMERO_TRANSACCION)), SUM(SUBTOTAL_LINEA), 0)

In the measure, I group it by Transaction Number, since a branch has more than one transaction number. In short, the Subtotal should show the values ​​contained in the MXN Subtotal.

I hope this clarifies your questions.

[henrikalmen]

Can you alter the field subtotal_linea in loadscript so that the incorrect values from the DB are corrected in the qlik data model? Then you won't need to do workarounds in frontend.

I am - naturally - not familiar with your data, but maybe in this case you should write your expression like this? If(not IsNull(aggr(SUM(SUBTOTAL_LINEA), NUMERO_TRANSACCION)), SUM(SUBTOTAL_LINEA), 0)

Stupid question, but I'll ask it anyway: if you have correct values in [Sub Total MXN] (you say "the Subtotal field should show the same values ​​as the MXN Subtotal field"), can't you use the same expression as there..? But I'm probably misunderstanding something in that you need another expression to work for other currrencies or something.