SUMCT Function Not Working on Formula

[Former Member]

Hello Experts,

I have created a Formula say Logic 1 ..

Formula is   I have One Keyfigure whose value is always 1 or 0

I check keyfigure >=1  Then Do Credit -Debit..

I am excluding those values which are 0 in key figure..

Logic1 value total is 8617.100 Which I achieved calculate results as Summation.

But I want to store this value as I applied sumct on Logic1 but its not giving me correct sum  it is giving  8782.1

But actual value is 8617.100

I have tried exception aggregation but its not working..

Attach is report screen short..  First Arrow from Left is for sumct and second is for logic1.. 

Or you can help me in other way.  I do not want my formula to be execute on Result..is there any way to handle this..

[anshu_lilhori · Accepted solution]

Hi,

You need to apply Nested Exception aggregation on your Logic1 formula.

First apply with the second char in Rows and then put this is another formula then apply with the third char in Rows.

Exception aggregation as Summation--reference characteristic as i mentioned above.

Hope this gives the correct total.

Regards,

AL

[Former Member · Accepted solution]

Hello Anshu..

It really solved my Problem.

But I am not able to understand why we use two times exception aggregation.

Really thanks .

But please let me know why we use  two times..

[Former Member · Accepted solution]

Hello Anshu

I have one more question.  I do have three characterstics in Rows.

0calmonth, Reservation numver, reservation Item

That was detail report.  But my User want sumarry report in which i will use only 0calmonth.

I did same way you recomend me and I achieved because of three characterstics but now in sumary I have only one 0calmonth . How will I achieve this...

[anshu_lilhori · Accepted solution]

Hi Mohit,

First of all i am happy that you've got the desired output.Now coming to your concerns definitely i will try my best to put my knowledge here.

Basically you need to identify the characteristic in case of Exception aggregation which makes your data unique.

So for your ease lets call your second rows as document no and third as its item.

If i apply only on document number --then as you can see against one document you have n no.of line items so it doesn't make my record unique.

And if i apply only on item then same item no.is there again different document no so again it doesnt make it unique.

But the combination of both document and item in your case its apparent that it makes a unique record.

For making it more easy treat nested exception aggregation as the concatenation of two characteristic.

Hope this helps you understanding the concept more deeply.

Regards,

AL

[anshu_lilhori · Accepted solution]

Hi Mohit,

I have explained in detail regarding the behavior of exception aggregation so now you can play around with it.

Remove the reservation no and item from drill down and hopefully the result should not change as we have already applied Exception aggregation.

Regards,

AL

[Former Member · Accepted solution]

Hello Anshul,

Thanks a Lot... You really save my whole day today.

Thanks Buddy.

Do let me know how I  can assign you points.. I have marked already correct answer..

you were right . Removing two other characters didn't change my Output..

Thanks,

Mohit.

[anshu_lilhori · Accepted solution]

Hi,

Great to know that..In a thread you can mark one answer as correct one and rest all as helpful answers if you feel they were.

Regards,

AL

[former_member182470]

Did you try with "Standard Aggregation" for your "Logic 1" ? Do not use summation.