Row Cumulative calculation in a Crosstab 1

[jonav]

Hi,

I have a Crosstab with Fiscal year as column , Bex structure as row and Amount as Summary.

I'm trying to do a cumulative calculation for specific rows in a crosstab.

Pls guide.

Thanks
JN

 

[jonav]

It says : "Boolean required here"


thanks,
JN

 

[jonav]

and also the values in previous page of cumulative is adding into next project number and displaying wrong values.

if Total discount has 100 100 and
cumulative will be 100 200
in a project.

where as with above formula this 200 of cumulative is carrying forward to next project number and so on..

is there anyway that these values are added only by project than carrying forward.

Thank
JN

 

[lbass]

Sorry, add "false" at the end:

whileprintingrecords;
numbervar cumul := 0;
false

-LB

 

[jonav]

ahh... TQ so much Ibass..

It worked like magic..

another thing..

I need to format the number. _ve values with braces and no thousand seperator.


Thanks,

JN

 

[lbass]

Change the display string formula to:

whileprintingrecords;
numbervar cumul;
if gridrowcolumnvalue("table.structure")="Cumulative Total Discounted Project Cost Cash Flows" then
(
if cumul <0 then
"("+ totext(cumul*-1,0,"")+")" else
totext(cumul,0,"")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,"")+")" else
totext(currentfieldvalue,0,"")
)

-LB

 

[jonav]

just a clarification.

Can I write this formula for multiple rows.

Like the below:

whileprintingrecords;
numbervar cumul;
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")in
["Cumulative Total Discounted Project Cost Cash Flows",
"Cumulative Total Discounted After Tax Return on Project","Cumulative Net Discounted After Tax Cash Flows"] then
(
if cumul <0 then
"("+ totext(cumul*-1,0,"")+")" else
totext(cumul,0,"")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,"")+")" else
totext(currentfieldvalue,0,"")
)

 

[lbass]

I think that should work--as long as you also add the other values into the initial formula. Did you try it?

-LB

 

[lbass]

Sorry, no that won't work because of the order of processing. You need to have a different variable name for each row you want to accumulate. You can do this in the same formula like this:

//{@accum} in suppression x+2 area:
whileprintingrecords;
numbervar x;
numbervar y;
numbervar z;
if gridrowcolumnvalue("table.structure") = "ABC" then
x := x + currentfieldvalue;
if gridrowcolumnvalue("table.structure") = "DEF" then
y := y + currentfieldvalue;
if gridrowcolumnvalue("table.structure") = "GHI" then
z := z + currentfieldvalue;

//{@display} in display string area:
whileprintingrecords;
numbervar x;
numbervar y;
numbervar z;
if gridrowcolumnvalue("table.structure")="ABC" then
(
if x <0 then
"("+ totext(x*-1,0,"")+")" else
totext(x,0,"")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,"")+")" else
totext(currentfieldvalue,0,"")
) else
if gridrowcolumnvalue("table.structure")="DEF" then
(
if y <0 then
"("+ totext(y*-1,0,"")+")" else
totext(y,0,"")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,"")+")" else
totext(currentfieldvalue,0,"")
) else
if gridrowcolumnvalue("table.structure")="GHI" then
(
if z <0 then
"("+ totext(z*-1,0,"")+")" else
totext(z,0,"")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,"")+")" else
totext(currentfieldvalue,0,"")
)

-LB

 

[jonav]

It is not taking the Else statements.. When I end the loop on every if condition then there is no result.

Pls help

thanks
JN

 

[lbass]

Please post your exact formula.

-LB

 

[jonav]

whileprintingrecords;
numbervar cumul;
numbervar cumultot;
numbervar cumulnet;

if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted Project Cost Cash Flows"
then
(
if cumul <0 then
"("+ totext(cumul*-1,0,",")+")" else
totext(cumul,0,",")
)
else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
);
//else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted After Tax Return on Project"
then
(
if cumultot <0 then
"("+ totext(cumultot*-1,0,",")+")" else
totext(cumultot,0,",")
)
else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
) ;
//else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Net Discounted After Tax Cash Flows"
then
(
if cumulnet <0 then
"("+ totext(cumulnet*-1,0,",")+")" else
totext(cumulnet,0,",")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)

 

[jonav]

and also Is it possible to display "0" as "-"?

Thanks
JN

 

[lbass]

Try this:

whileprintingrecords;
numbervar cumul;
numbervar cumultot;
numbervar cumulnet;

if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted Project Cost Cash Flows"
then
(
(
if cumul <0 then
"("+ totext(cumul*-1,0,",")+")" else
totext(cumul,0,",")
)
else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted After Tax Return on Project"
then
(
(
if cumultot <0 then
"("+ totext(cumultot*-1,0,",")+")" else
totext(cumultot,0,",")
)
else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Net Discounted After Tax Cash Flows"
then
(
(
if cumulnet <0 then
"("+ totext(cumulnet*-1,0,",")+")" else
totext(cumulnet,0,",")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
)

PS. Your reset formula should reset all three variables, too.

-LB

 

[jonav]

Tried,

It says ")" is missing at the else statement which is highlighted.

whileprintingrecords;
numbervar cumul;
numbervar cumultot;
numbervar cumulnet;

if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted Project Cost Cash Flows"
then
(
(
if cumul <0 then
"("+ totext(cumul*-1,0,",")+")" else
totext(cumul,0,",")
)
"==else"""" [")"is missing]
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted After Tax Return on Project"
then
(
(
if cumultot <0 then
"("+ totext(cumultot*-1,0,",")+")" else
totext(cumultot,0,",")
)
else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Net Discounted After Tax Cash Flows"
then
(
(
if cumulnet <0 then
"("+ totext(cumulnet*-1,0,",")+")" else
totext(cumulnet,0,",")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)
)

 

[lbass]

I'll test this in a couple hours at home.

-LB

 

[jonav]

TQ Ibass, will wait for your reply.

Thanks,
JN

 

[lbass]

The display formula should be:

whileprintingrecords;
numbervar cumul;
numbervar cumultot;
numbervar cumulnet;

if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted Project Cost Cash Flows"
then
(
if cumul <0 then
"("+ totext(cumul*-1,0,",")+")" else
totext(cumul,0,",")
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Total Discounted After Tax Return on Project"
then
(
if cumultot <0 then
"("+ totext(cumultot*-1,0,",")+")" else
totext(cumultot,0,",")
) else
if gridrowcolumnvalue("ZGCB_M01_ZBOBJ_ZGCB_M01_QFIN_ANLSIS1_V7.[4LVE0F039ABA0HJHVHOLF3BQD]")= "Cumulative Net Discounted After Tax Cash Flows"
then
(
if cumulnet <0 then
"("+ totext(cumulnet*-1,0,",")+")" else
totext(cumulnet,0,",")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
totext(currentfieldvalue,0,",")
)

-LB

 

[jonav]

worked perfectly.

Thanks Ibass.

If I want ot display 0 as "-" then ?.

Thanks again
JN

 

[lbass]

Build in lines like this into each set:
(
if cumulnet <0 then
"("+ totext(cumulnet*-1,0,",")+")" else
if cumulnet = 0 then "-" else
totext(cumulnet,0,",")
) else
(
if currentfieldvalue<0 then
"("+totext(currentfieldvalue*-1,0,",")+")" else
if currentfieldvalue = 0 then "-" else
totext(currentfieldvalue,0,",")
)

-LB

 

[jonav]

Somehow not able to print "-" for other rows.

anyway.. no issues. I can convince the users on this.

Loads of thanks Ibass.

Thanks,
JN