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

[#1]

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.

[tresesco · Accepted solution]

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.

[Anil_Babu_Samineni]

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

OR

if(Len(Stock & pks)>0

OR

If((Stock & pks) > 0

[#4]

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.

[#5]

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