topic getting value wrong in straight and pivot table in QlikView

getting value wrong in straight and pivot table

Mon, 30 Apr 2012 09:27:16 GMT

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.

getting value wrong in straight and pivot table

swuehl — Mon, 30 Apr 2012 17:30:46 GMT

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.

Re: getting value wrong in straight and pivot table

Wed, 02 May 2012 12:00:47 GMT

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

getting value wrong in straight and pivot table

swuehl — Wed, 02 May 2012 21:43:46 GMT

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?

getting value wrong in straight and pivot table

Thu, 03 May 2012 05:45:53 GMT

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

getting value wrong in straight and pivot table

Anonymous — Thu, 03 May 2012 07:51:54 GMT

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.

getting value wrong in straight and pivot table

Thu, 03 May 2012 09:15:55 GMT

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

getting value wrong in straight and pivot table

Anonymous — Thu, 03 May 2012 11:33:10 GMT

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

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

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

Dennis.

getting value wrong in straight and pivot table

Anonymous — Thu, 03 May 2012 11:35:21 GMT

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

Is that an option?