topic Re: Numbers Mismatch Between Straight and bar Chart in QlikView

Numbers Mismatch Between Straight and bar Chart

amit_saini — Tue, 08 May 2018 14:16:00 GMT

Hi Folks.

I'm using below Expression in straight table , which is giving me right numbers of Cost:

Straight Table:

Dim: KSTAR

Income_Statement_Line'

Expression=num(if(Main_Order='71410',sum({<Main_Order={'71410','75413'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71501',sum({<Main_Order={'71501','75501'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71502',sum({<Main_Order={'71502','75502'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost) ,

if(Main_Order='71503',sum({<Main_Order={'71503','75512'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71601',sum({<Main_Order={'71601','75614'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

sum({<Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost)))))),'#,##0')

but when I'm calling same expression into stacked bar chart , it looks like together numbers are not correct.

Stacked Bar Chart:

Dim: Project Type

Income_Statement_Line'

Express: as Above

Might be Aggr sum is required , I tried but stoll no sucess.

Please help!

Regards,

Re: Numbers Mismatch Between Straight and bar Chart

amit_saini — Wed, 09 May 2018 06:18:02 GMT

Guys any suggestion?

Regards,

Re: Numbers Mismatch Between Straight and bar Chart

jonathandienst — Wed, 09 May 2018 06:25:05 GMT

>>it looks like together numbers are not correct.

Please be more specific about what you believe to be incorrect. I can't see any wrong numbers in the sample you uploaded.

Re: Numbers Mismatch Between Straight and bar Chart

amit_saini — Wed, 09 May 2018 06:50:43 GMT

Hi Jonathan,

For Main Order = 71601 , correct cost numbers are 2,782,512

So result in Straight table is correct , but when I'm calling same expression in stacked bar chart, I'm having cost total as 2569688.76 (Export bar chart to excel).

It has to be the same as above straight table cost result.

See below Main Order 71601 has a Vpart as 75614 , so in general when I select Main_Order "71601" from list than it should sum up the Cost for both Main Order (71601) and it's corresponding Vpart (75614) .

Main_Order	Vpart
71601	75614

Note: All the numbers in Vpart field are available inside "Main_Order" itself.

To achieve this I'm using below expression in straight chart and Output is correct:

if(Main_Order='71410',sum({<Main_Order={'71410','75413'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost)

I have used above expression , because we are having only 4 cases where Main_Order has Vpart number.

....... similar for

Main_Order Vpart

71501 75501

71502 75502

Hope it is more clear now!

Thanks,

Re: Numbers Mismatch Between Straight and bar Chart

amit_saini — Wed, 09 May 2018 13:33:54 GMT

Any suggestions???

Regards,

Re: Numbers Mismatch Between Straight and bar Chart

sunny_talwar — Wed, 09 May 2018 14:18:56 GMT

Not entirely sure, but may be this

=num(if(Main_Order='71410',sum(TOTAL <Income_Statement_Line>{<Main_Order={'71410','75413'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71501',sum(TOTAL <Income_Statement_Line>{<Main_Order={'71501','75501'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71502',sum(TOTAL <Income_Statement_Line>{<Main_Order={'71502','75502'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost) ,

if(Main_Order='71503',sum(TOTAL <Income_Statement_Line>{<Main_Order={'71503','75512'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

if(Main_Order='71601',sum(TOTAL <Income_Statement_Line> {<Main_Order={'71601','75614'},Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost),

sum(TOTAL <Income_Statement_Line>{<Income_Statement_Line-={'A - Gross sales','B - Sales Deductions','D - Change in stock / Cap. work & mat.'}>}Cost)))))) * Avg(1),'#,##0')