Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart automatically averaging out data

Hi everyone,

Still new at this...Not sure why the graph is automatically taking the average of my data. Is there a way to chart only the data that is in the table even if calculations are being done behind the scenes. The straight table says one thing the chart is different.

The only expression I have is "% of total cost by BU: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST) "

First I have no year selected. When I chart this it takes the average of the years and displays the activity along the "x" and percentage on the "y." So for activity 1 the average is around 14.72% and Activity 2 is .02%

ActivityBusiness UnitYear% of Total Cost by BU
Activity 1BU1200814.46%
Activity 1BU1200714.94%
Activity 2BU120080.02%
Activity 2BU120070.02%


Next I select 2007. Table automatically changes to below.

ActivityBusiness UnitYear% of Total Cost by BU
Activity 1BU1200714.94%
Activity 2BU120070.02%

But when I chart this graph it shows the percentage of Activity 1 to be 8.14%. It is counting the sum of costs for 2008 as zero and than taking the average bringing it down to 8.14%. If I select 2007,I want it to chart just 2007 without taking an average. The straight table shows the data I want but the chart is the average with 2008 as zero.

How can I force it to chart 14.94% or am I doing this completely wrong. Thanks!



5 Replies
boorgura
Specialist
Specialist

Ideally a trend chart(bar/line) should not have more than 2 dimensions.

That is why you might not get the desired.

Not applicable
Author

Thanks Rocky. I included year to help make it easier to explain but even with just two dimensions (activity, business unit) it still has the same problem.

Is there a different way to write the expression? I need to know the percentage of (activity dollars)/(total dollars in the business unit). This needs to the case for all dollars in 2007 and 2008 or by each specific year if the user wants to see it individually. The chart doesn't need to show they year because users will know what year(s) has been selected.

johnw
Champion III
Champion III

I'm not exactly following what you want, but I believe you need to use TOTAL instead of ALL. ALL ignores selections.

Not applicable
Author

Thanks John.

TOTAL doesn't work because it dispalys the percentage in terms of the selection and I want it to give the percentage regardless of what drill-down or selections are made.

This is an example:

1. Raw data

ActivityBUYearCost
Activity 1BU1200810
Activity 1BU1200730
Activity 2BU1200825
Activity 2BU1200740
Activity 3BU1200810
Activity 3BU1200730
Activity 1BU2200810
Activity 1BU2200720
Activity 2BU2200830
Activity 2BU2200740
Activity 3BU2200880
Activity 3BU2200730


If the user selects no year the table should show

ActivityBUYearCostBU Total% of Total Cost by BU
Activity 1BU12007 and 20084014527.59%
Activity 2BU12007 and 20086514544.83%
Activity 3BU12007 and 20084014527.59%100.00%
Activity 1BU22007 and 20083021014.29%
Activity 2BU22007 and 20087021033.33%
Activity 3BU22007 and 200811021052.38%100.00%


If the user selects 2007 the table should be

ActivityBUYearCostBU Total% of Total Cost by BU
Activity 1BU120073010030.00%
Activity 2BU120074010040.00%
Activity 3BU120073010030.00%100.00%
Activity 1BU22007209022.22%
Activity 2BU22007409044.44%
Activity 3BU22007309033.33%100.00%


These tables are working with the current expression. % of Total: Sum(COST)/Sum(ALL <"Year","Business Unit"> COST)

However when I create the chart off of 2007 (the above table) it automatically graphs the bottom data because the cost of 2008 is zero but is included in the BU Total.

ActivityBUYearCostBU Total% of Total Cost by BU
Activity 1BU120073014520.69%
Activity 2BU120074014527.59%
Activity 3BU120073014520.69%68.97%
Activity 1BU22007202109.52%
Activity 2BU220074021019.05%
Activity 3BU220073021014.29%42.86%


Appreciate any help. Thanks.

johnw
Champion III
Champion III

Still confused. You seem to say you want to use ALL because you "want it to give the percentage regardless of what drill-down or selections are made." But then you seem to complain about the bottom chart "because the cost of 2008... is included in the BU Total". It seems you instead want the first two charts, not the third chart. And you can generate the first two charts by using TOTAL instead of ALL. Specifically:

Cost = sum(Cost)
BU Total = sum(TOTAL <BU> Cost)
% of Total Cost by BU = Cost/"BU Total"

See attached. Is that not what you want?