Cross table problem

[narender123]

Hi All,

I am using a cross table.

Like:-

TableA:

    DECLARATION_ID,

   CUSTOMS_VALUE

     ITEM_NO,

    EXPORT_DUTY_EXEMPTED,

     EXCISE_DUTY_EXEMPTED

FROM TABLEA;

Expression in report:

=Sum(CUSTOMS_VALUE) is showing right.

but when use cross table

CrossTable(TAX_CODE,TAX,3)

NEW:

LOAD DECLARATION_ID,

        CUSTOMS_VALUE

       ITEM_NO,

       EXPORT_DUTY_EXEMPTED,

       EXCISE_DUTY_EXEMPTED

RESIDENT TableA;

DROP TABLE TableA;

Expression in report:

=Sum(CUSTOMS_VALUE) is showing wrong.

Why after cross table CUSTOMS_VALUE is showing wrong??

Thanks,

Narender

[Anonymous]

Could you share your excel table or sample qvw?

Tax Code exist into your table?

Why you are applying Cross Table on same table after taking resident of same table?

check this:

The Crosstable Load

What is cross table? Where it can be used?

[sunny_talwar · Accepted solution]

Because after the CrossTable() Load  the field CUSTOMS_VALUE will repeat and you will get a much higher number. Try this after CrossTable() Load:

Sum(Aggr(Only(CUSTOMS_VALUE), DECLARATION_ID))

[#4]

you can use aggr function

[narender123]

Thanks Sunny.Its working.

Could you tell me , use of "Only" in aggr?

[sunny_talwar]

So since the same number for CUSTOMS_VALUE is repeating for a single DECLARATION_ID, I just used the only function to get its value. You can use Avg(CUSTOMS_VALUE) or Sum(DISTINCT CUSTOMS_VALUE) and they will give you the exact same output.

Sum(Aggr(Only(CUSTOMS_VALUE), DECLARATION_ID))

Sum(Aggr(Avg(CUSTOMS_VALUE), DECLARATION_ID))

Sum(Aggr(Sum(DISTINCT CUSTOMS_VALUE), DECLARATION_ID))

[narender123]

Got it.

Thanks,

[narender123]

Thanks Balraj.

[sunny_talwar]

Awesome

If your issue is resolved, can you close the thread by marking correct answer.

Best,

Sunny

[Anonymous]

I have not done anything, say thanks to Sunny