TO SHOW DECMBER BALANCES IN TOTAL COLUMN

[nevilledhamsiri]

Hi Friends,

What expression be used to show balances in December in the total column? Data sent presented below.

Thanks

Neville

CODE	MONTH	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	Total
99999	PROVISION	250,000	255,000	262,500	272,500	273,000	272,250	262,250	267,250	272,250	277,250	283,250	290,250	290,250
99999	ADJUSTMENT	5,000	7,500	10,000	500	(750)	(10,000)	5,000	5,000	5,000	6,000	7,000	6,000	6,000
99999	CLOSING	255,000	262,500	272,500	273,000	272,250	262,250	267,250	272,250	277,250	283,250	290,250	296,250	296,250

[Anil_Babu_Samineni]

You should create inline/island table

Island:

Load * Inline [

Dim

1

2

3

];

Create Dimension

CODE

Pick(Dim, MONTH, 'TOTAL')

Expression will be

Pick(Dim, Sum(Amount), Sum({<MONTH={'DEC'}>} Amount))

Or

Pick(Dim, Sum(Amount), Sum({<MONTH={$(Max(MONTH))}>} Amount))

[nevilledhamsiri]

Hi Anil,

Sorry for delay in replying. Thanks for providing a solution . Could you please check my qvd & help me to bring in your solution to suit there. What I basically need is the total column should be amended to present UPR B/F & UPR C/F ,JAN balance & DEC balance respectively. Right now what is there is the sum of all Months balances. But all other variables should be the sum of 12 Months calculated as it is.

Thanks

Neville

[MayilVahanan · Accepted solution]

Hi @nevilledhamsiri 

Are you looking for Total Column need to display only Jan & Dec month alone for few KPIs? In that case, try like below

If(ColumnNo()=0,
SUM({<ITEM={'UPR_PROVISION'}, MONTH={'JAN', 'DEC'}>}AMOUNT),
SUM({<ITEM={'UPR_PROVISION'}>}AMOUNT))

[nevilledhamsiri]

Hi, MayiVahana

Thanks for the reply. What I need is while showing all other KPIs as it is ,in the total column (sum of Jan to Dec) for UPR B/F & UPR C/F ,should be shown in the total column like below with other KPIs.

Premium----3646233

Ceeded Premium---89423

UPR B/F------27789 (Jan opening Balance)

UPR C/F-----10397 (Dec Closing balance)

UPR Charge---17392 (Difference between UPR Opening & Closing)

What should be appeared in the total column should be what I have given above. Your formula once applied it gets the addition of both Jan & Dec balance in the closing. Could you please suggest an expression which will bring the variables in the total column as given above.

Thank you very much

Neville

[nevilledhamsiri]

Hi, MayiVahana

I was able to modify the expression based on your guidance to suit to my exact requirement. Thanks a lot for the helping hand. Attached  the qvd after modified formula which gives me what I need exactly.

Thanks

Neville