Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

getting value wrong in straight and pivot table

in expression i have used aggr function on country and region name, i get value 99.24 in straight table whereas 90.23 in pivot table.

8 Replies
swuehl
MVP
MVP

It's really hard to say anything.

Could you post a small sample app or at least your dimensions / expressions / chart settings?

Also a screen shot might be helpful.

One usual suspect is that in a straight chart, you can select a total mode like 'average', while in a pivot, the total mode is always expression total.

Not applicable
Author

expression used in bar chart as well as in line chart

=avg(aggr(if(vMA_by=0,

if(WRD_VAR=0,

avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_DOC),

if(WRD_VAR=1,

avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_COD)

)),

if(vMA_by=1,

if(WRD_VAR=0,

avg({$<Gen_Yr_Mn=  {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}MAX_Z5_MA_DOC),

if(WRD_VAR=1,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Z_MAX_Z5<>0,if(Gen_DATE>=MA_COD,Z_Z4),0))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=CODand Z_MAX_Z5<>0,Z_MAX_Z5,Null()))*100)),

if(vMA_by=2,

if(WRD_VAR=0,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,Z_Z4))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100,

if(WRD_VAR=1,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_COD,Z_Z4))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=COD,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100

))))),Gen_Month_Name,COMP_CODE,COUNTRY_NAME,REGION_NAME_SHORT,PLANT_NAME,WTG_RATING,Model,CONTROLER_TYPE,BLADE_TYPE,CUST_NAME_DESC))

dimension in Bar chart-Region_Name_short

dimension in line chart- if(Gen_Yr_Mn >=$(vlast12MonthYr) and Gen_Yr_Mn <= $(vmaxMasterYrMn) and Region_Name_Short='GUJ', GenDate_Mn)

where:-

vMinMasterYrMn- =max(Master_Year)&04

vMaxMasterYrMn- =date(max(Date),'YYYY')&date(max(Date),'MM')

vLast12MonthYr - =date(addmonth(date#(max(cal_Yr_Mn,'YYYYMM'), -11, YYYYMM,)


swuehl
MVP
MVP

Sorry, I am lost.

Haven't you reported a problem with straight vs. pivot table chart in your original post?

If you say: 'value 99.24', is this a total value or an expression value for a single line?

Not applicable
Author

expression used in linechart ,pivot table ,straight table

=avg(aggr(if(vMA_by=0,

if(WRD_VAR=0,

avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_DOC),

if(WRD_VAR=1,

avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_COD)

)),

if(vMA_by=1,

if(WRD_VAR=0,

avg({$<Gen_Yr_Mn=  {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}MAX_Z5_MA_DOC),

if(WRD_VAR=1,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Z_MAX_Z5<>0,if(Gen_DATE>=MA_COD,Z_Z4),0))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=CODand Z_MAX_Z5<>0,Z_MAX_Z5,Null()))*100)),

if(vMA_by=2,

if(WRD_VAR=0,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,Z_Z4))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100,

if(WRD_VAR=1,

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_COD,Z_Z4))/

sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=COD,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100

))))),Gen_Month_Name,COMP_CODE,COUNTRY_NAME,REGION_NAME_SHORT,PLANT_NAME,WTG_RATING,Model,CONTROLER_TYPE,BLADE_TYPE,CUST_NAME_DESC))

dimension in line chart,Pivot table,straight table- if(Gen_Yr_Mn >=$(vlast12MonthYr) and Gen_Yr_Mn <= $(vmaxMasterYrMn) and Region_Name_Short='GUJ', GenDate_Mn)

where:-

vMinMasterYrMn- =max(Master_Year)&04

vMaxMasterYrMn- =date(max(Date),'YYYY')&date(max(Date),'MM')

vLast12MonthYr - =date(addmonth(date#(max(cal_Yr_Mn,'YYYYMM'), -11, YYYYMM,)


SO WHEN I DO FAST CHANGES BETWEEN LINE CHART , PIVOT TABLE AND STRAIGHT TABLE THE VALUES DIFFER BETWEEN STRAIGHT TABLE AND PIVOT TABLE(LINE CHART)

SO PLEASE HELP ME OUT

Anonymous
Not applicable
Author

Just to make sure; do you have the Supress When Value is Null checked on your dimension tab?

this can cause a different between a table charts and a pivot table when one or more of your dimensions is empty.

Not applicable
Author

I have supress the null value but still the value are different.

Anonymous
Not applicable
Author

I would suggest that you simplefy your options to find out where your problem lies.

Fe

change your calculated dimension for just one dimension and see if the outcome is still the same.

Or

change your experssion to a simple sum and see if the outcome is still the same.

Dennis.

Anonymous
Not applicable
Author

It would be easier if you could share yourr Qlikview document so we can have a look.

Is that an option?