Pivot totals are not matching

[paulwalker]

Hi Community,

Facing issue with totals on Povit table.

Could you please help me...

my Expression is:

IF(PeriodType='YTD' and Status='Opening',
Sum({<Month = {"$(=Month(YearStart(Today())))"}>} Value),

IF(PeriodType='YTD',
Sum({<Period = {">=$(=Date(YearStart(Max(Period)),'DD/MMM/YY'))<=$(=Date(MonthEnd(Today()),'DD/MMM/YY'))"}, Month=>} Value)))

Totals are not matching....

Thanks in Advance!

[stevejoyce · Accepted solution]

What if you try this...

IF(PeriodType='YTD',
    Sum({<Status = {'Opening'}, Month = {"$(=Month(YearStart(Today())))"}>} Value)

    +

    Sum({<Status -= {'Opening'}, Period = {">=$(=Date(YearStart(Max(Period)),'DD/MMM/YY'))<=$(=Date(MonthEnd(Today()),'DD/MMM/YY'))"}, Month=>} Value)

)

[Or]

If I read this correctly, for the Total row, only the second part of the If() statement will be evaluated, as the status isn't 'Opening' (there's multiple statuses so the condition evaluates to false). You'd need to either rewrite the formula so that the if() statements are inside the sum() statements (and thus evaluated for each line individually), or use Dimensionality() to add a separate formula for the Total.

[stevejoyce]

I agree with @Or .  That's why i merged into 1 single expression adding them together, but status in set analysis to only evaluate as desired.  But that should do it.