topic Pivot Table With AGGR in QlikView

Pivot Table With AGGR

dineshm030 — Thu, 19 Aug 2021 16:43:23 GMT

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.

Re: Pivot Table With AGGR

tincholiver — Fri, 20 Aug 2021 12:52:47 GMT

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

Re: Pivot Table With AGGR

MayilVahanan — Sat, 21 Aug 2021 02:24:24 GMT

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

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

Re: Pivot Table With AGGR

dineshm030 — Mon, 23 Aug 2021 13:42:29 GMT

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:

Kindly help me out.

Regards,

Dinesh Kumar.

Re: Pivot Table With AGGR

MayilVahanan — Tue, 24 Aug 2021 00:49:11 GMT

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

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

in aggr fn, you need to add contactname also.

Its depends on ur requirement