Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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,)
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?
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
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.
I have supress the null value but still the value are different.
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.
It would be easier if you could share yourr Qlikview document so we can have a look.
Is that an option?