Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master III
Master III

Numbers Mismatch Between Straight and bar Chart

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,

AS

5 Replies
Highlighted
Master III
Master III

Re: Numbers Mismatch Between Straight and bar Chart

Guys any suggestion?

Regards,

AS

MVP
MVP

Re: Numbers Mismatch Between Straight and bar Chart

>>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.


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Master III
Master III

Re: Numbers Mismatch Between Straight and bar Chart

Hi Jonathan,

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

X.PNG

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_OrderVpart
7160175614

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,

AS

Highlighted
Master III
Master III

Re: Numbers Mismatch Between Straight and bar Chart

Any suggestions???

Regards,

AS

Highlighted

Re: Numbers Mismatch Between Straight and bar Chart

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')