Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

TO SHOW DECMBER BALANCES IN TOTAL COLUMN

 

Hi Friends,

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

 

Thanks

 

Neville

 

CODEMONTHJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTotal
99999PROVISION  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
99999ADJUSTMENT      5,000      7,500    10,000         500        (750)  (10,000)      5,000      5,000      5,000      6,000      7,000       6,000       6,000
99999CLOSING  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
1 Solution

Accepted Solutions
MayilVahanan

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

MayilVahanan_0-1606097667278.png

 

 

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

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.

 

 

 

nevilledhamsiri_0-1606062354289.png

 

Thanks

Neville

MayilVahanan

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

MayilVahanan_0-1606097667278.png

 

 

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
nevilledhamsiri
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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