Pivot Table With AGGR

[dineshm030]

Hi All,

I am developing the Pivot table for no of FD calculation. I am facing the issue with below expression.

Expression:

Sum(Aggr((Sum({$<Month_End_Flag = {'-1'}>} Day_Number_In_Month) - Sum(TOTLength) -
(Count({$<Day_of_Week_Eng = {'Sunday'}>}Day_of_Week_Eng) - Count({<APWeekDay = {'Sun'},APStateCode = {'1'}>}Distinct APEndDate))),FFOptimaV3TerritoryIDDescription))

Without Month Selection, it is showing only Jan month with full month data. It will be splitting the month wise in pivot table. It should show for all months.

Kindly help me out.

Regards,

Dinesh Kumar.

[tincholiver]

try this

Sum(Aggr(DISTINCT (Sum({$<Month_End_Flag = {'-1'}>} Day_Number_In_Month) - Sum(TOTLength) - (Count({$<Day_of_Week_Eng = {'Sunday'}>}Day_of_Week_Eng) - Count({<APWeekDay = {'Sun'},APStateCode = {'1'}>}Distinct APEndDate))),FFOptimaV3TerritoryIDDescription))

[MayilVahanan · Accepted solution]

Hi

In Aggr function, you need to mention the month field also

Sum(Aggr((Sum({$<Month_End_Flag = {'-1'}>} Day_Number_In_Month) - Sum(TOTLength) -
(Count({$<Day_of_Week_Eng = {'Sunday'}>}Day_of_Week_Eng) - Count({<APWeekDay = {'Sun'},APStateCode = {'1'}>}Distinct APEndDate))),FFOptimaV3TerritoryIDDescription, urmonthfield))

And also, make sure, all the month satisfy the set analysis info

[dineshm030]

Hi,

While i am seeing the data at FF Name it is showing the value,

while I am looking the data at Contact level it is showing as 0.

But I am checking the one contact name the value is showing,

Expression:

Sum(Aggr((Sum({$<Month_End_Flag = {'-1'}>} Day_Number_In_Month) - Sum(TOTLength) -
(Count({$<Day_of_Week_Eng = {'Sunday'}>}Day_of_Week_Eng) - Count({<APWeekDay = {'Sun'},APStateCode = {'1'}>}Distinct APEndDate))),Month,FFOptimaV3TerritoryIDDescription))

Kindly help me out.

Regards,

Dinesh Kumar.

[MayilVahanan]

Hi

I think, you can try without aggr function. 

Sum({$<Month_End_Flag = {'-1'}>} Day_Number_In_Month) - Sum(TOTLength) -
(Count({$<Day_of_Week_Eng = {'Sunday'}>}Day_of_Week_Eng) - Count({<APWeekDay = {'Sun'},APStateCode = {'1'}>}Distinct APEndDate))

or

If its based on FFPosition alone, try to add Total<FFPosition>

or

in aggr fn, you need to add contactname also.

Its depends on ur requirement