topic Data incorrect in pivot table while it is correct in Straight table in QlikView

Data incorrect in pivot table while it is correct in Straight table

Wed, 25 Nov 2020 16:16:04 GMT

Greetings All,

I have searched in the community and I figured out that if we use an aggregate function, all the columns should be present in pivot table should be in the expression , but still my data is incorrect.

However when I change it to straight table and use expression as sum of rows it gives me correct values. Below is the query

if([Stock, pks]>0,
sum(aggr(
If(Seasonal='Seasonal',
        sum({<Submission={'Actual'},Month_End_Date={">=$(Start_Date) <=$(End_Date)"},FA={'TIMS sales out'}>} Quantity/12),
        if (Seasonal='Non-seasonal',
               sum({<Submission={'Actual'},Month_End_Date={">=$(Start_Date_3M) <=$(End_Date)"},FA={'TIMS sales out'}>} Quantity/3),

               if ([Distr Forecast]=1,
                      if (Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)>0,
                            sum({<Submission={'Actual', 'BL FCST'},Month_End_Date={">$(End_Date) <$(End_date_3M)"},FA={'TIMS sales out'}>} Quantity/3)
                      ),
                      if (Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)>0,

                            Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)/
                            Sum (TOTAL <Presentation> {$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)*
                            sum(TOTAL <Presentation>{<Submission={'Actual', 'BL FCST'},Month_End_Date={">$(End_Date) <$(End_date_3M)"},FA={'TIMS sales out'}>} Quantity/3)
                     )
               )
         )
)
,
Brand, Distributor )
)
)

I am using Brand and Distributor as dimensions in pivot table and an expression to represent the measure. Any help is much appreciated.

Thanks in Advance.

Re: Data incorrect in pivot table while it is correct in Straight table

tresB — Fri, 28 Oct 2016 04:33:29 GMT

I am not sure, but worth a try I guess. Try removing the IF and incorporating the condition in the sum() like:

sum({<[Stock, pks]={'>0'}>} aggr(
If(Seasonal='Seasonal',
        sum({<Submission={'Actual'},Month_End_Date={">=$(Start_Date) <=$(End_Date)"},FA={'TIMS sales out'}>} Quantity/12),
        if (Seasonal='Non-seasonal',
              sum({<Submission={'Actual'},Month_End_Date={">=$(Start_Date_3M) <=$(End_Date)"},FA={'TIMS sales out'}>} Quantity/3),

              if ([Distr Forecast]=1,
                      if (Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)>0,
                            sum({<Submission={'Actual', 'BL FCST'},Month_End_Date={">$(End_Date) <$(End_date_3M)"},FA={'TIMS sales out'}>} Quantity/3)
                      ),
                      if (Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)>0,

                            Sum ({$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)/
                            Sum (TOTAL <Presentation> {$<Year={$(#=Max(Selected_Year))},Month={$(#=Max(Selected_Month))},Submission={'Actual'},FA={'TIMS stocks'}>}Quantity)*
                            sum(TOTAL <Presentation>{<Submission={'Actual', 'BL FCST'},Month_End_Date={">$(End_Date) <$(End_date_3M)"},FA={'TIMS sales out'}>} Quantity/3)
                    )
              )
        )
)
,
Brand, Distributor )
)

If this doesn't work, try putting the set part in red in all the inner sum()s. Else, try to share your sample qvw.

Re: Data incorrect in pivot table while it is correct in Straight table

Anil_Babu_Samineni — Fri, 28 Oct 2016 05:21:39 GMT

Do you get any Type Error. This seems ok to me

[Stock, pks] -- Does this is two fields

If not, Use this Might be this

If( Len([Stock, pks]) > 0

if(Len(Stock & pks)>0

If((Stock & pks) > 0

Re: Data incorrect in pivot table while it is correct in Straight table

Fri, 28 Oct 2016 07:37:56 GMT

Thanks a ton it works Amit.

I belive the expression failed for IF statement being out of aggregation. can you put more light on the change you did.

Re: Data incorrect in pivot table while it is correct in Straight table

Fri, 28 Oct 2016 07:38:45 GMT

Hi Anil, thanks for the reply [Stock, pks] is one column only