Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All..
I have two field items which i added via load inline (STAFF SALARY and CASUAL WAGE), since they were not included as the field values in the dimension named DESCRIPTION.
LOAD DESCRIPTION,
[DR AMT],
[CR AMT],
BRCOE,
[Print Time :],
0.46020833333022892,
[JURNAL VOUCHER LISTING],
[August - 2023],
BIYAGAMA,
F10,
ACCOUNT,
[DEBIT (Rs.)],
[CREDIT (Rs.)]
FROM
[F:\QV WORK\SALARY JE .xlsx]
(ooxml, embedded labels, table is SOURCE);
load *inline
[
DESCRIPTION,GL CODE
'STAFF SALARY','76110'
'CASUAL WAGE', '76130'
];
And I need to add Expression to these two items (STAFF SALARY and CASUAL WAGE) based on two field values included in the dimension (EPF 12 % , GRATIUTY A/C).
Calculation as follows.
My expected output looks like below
DESCRIPTION |
GL CODE |
GL NAME |
NARRATION |
DR |
CR |
BASIC SALARY |
76110 |
STAFF SALARY |
BASIC SALARY |
750000 |
0 |
BASIC SALARY |
41000 |
SALARY CONTROL |
BASIC SALARY |
|
750000 |
BASIC SALARY |
76130 |
CASUAL WAGE |
BASIC SALARY |
229167 |
0 |
BASIC SALARY |
41000 |
SALARY CONTROL |
BASIC SALARY |
|
229167 |
Work done up to now by me……………………………………………………………………………………………………………………..
DESCRIPTION |
GL CODE |
GL NAME |
NARRATION |
DR |
CR |
EPF 8 % |
41000 |
SALARY CONTROL |
DEDUCTIONS |
14088 |
0 |
EPF 8 % |
41200 |
EPF PAYABLE |
DEDUCTIONS |
0 |
14088 |
Stamp Duty |
41000 |
SALARY CONTROL |
DEDUCTIONS |
100 |
0 |
Stamp Duty |
44200 |
STAMP DUTY |
DEDUCTIONS |
0 |
100 |
MEDICAL FUND MEM.CON |
41000 |
SALARY CONTROL |
DEDUCTIONS |
1250 |
0 |
MEDICAL FUND MEM.CON |
41800 |
MEDICAL FUND |
DEDUCTIONS |
0 |
1250 |
PENSION FUND 01 |
41000 |
SALARY CONTROL |
DEDUCTIONS |
2500 |
0 |
PENSION FUND 01 |
41420 |
PENSION FUND |
DEDUCTIONS |
0 |
2500 |
SPORTS CLUB |
41000 |
SALARY CONTROL |
DEDUCTIONS |
125 |
0 |
SPORTS CLUB |
43200 |
SPORTS CLUB |
DEDUCTIONS |
0 |
125 |
UNION FEES |
41000 |
SALARY CONTROL |
DEDUCTIONS |
625 |
0 |
UNION FEES |
42800 |
UNION FEES |
DEDUCTIONS |
0 |
625 |
FESTIVAL LOAN - ( CAPITAL ) |
32800 |
FESTIVAL LOANPAYABLE |
DEDUCTIONS |
0 |
5258.33 |
FESTIVAL LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
5258.33 |
0 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
5268.67 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
5268.67 |
0 |
S.S PREMIUM LOAN - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
3326.1 |
S.S PREMIUM LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
3326.1 |
0 |
SUNDRY LOAN 5 - ( CAPITAL ) |
32600 |
SUNDRY LOAN PAYABLE |
DEDUCTIONS |
0 |
4841.67 |
SUNDRY LOAN 5 - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
4841.67 |
0 |
EPF 12 % |
41200 |
EPF PAYABLE |
STATUTORY PAY |
0 |
21132 |
EPF 12 % |
76150 |
EPF EXPENCE |
STATUTORY PAY |
21132 |
0 |
ETF 3% |
41400 |
EPF PAYABLE |
STATUTORY PAY |
0 |
5283 |
ETF 3% |
76170 |
ETF EXPENCE |
STATUTORY PAY |
5283 |
0 |
GRATIUTY A/C |
42000 |
GRATUITY PAYABLE |
STATUTORY PAY |
0 |
21132 |
GRATIUTY A/C |
76190 |
GRATIUTY EXPENCE |
STATUTORY PAY |
21132 |
0 |
MEDICAL FUND A/C |
37540 |
MEDICAL FUND |
STATUTORY PAY |
0 |
3750 |
MEDICAL FUND A/C |
76230 |
MEDICAL FUND |
STATUTORY PAY |
3750 |
0 |
EPF 8 % |
41000 |
SALARY CONTROL |
DEDUCTIONS |
78333.36 |
0 |
EPF 8 % |
41200 |
EPF PAYABLE |
DEDUCTIONS |
0 |
78333.36 |
Stamp Duty |
41000 |
SALARY CONTROL |
DEDUCTIONS |
400 |
0 |
Stamp Duty |
44200 |
STAMP DUTY |
DEDUCTIONS |
0 |
400 |
MEDICAL FUND MEM.CON |
41000 |
SALARY CONTROL |
DEDUCTIONS |
3000 |
0 |
MEDICAL FUND MEM.CON |
41800 |
MEDICAL FUND |
DEDUCTIONS |
0 |
3000 |
PENSION FUND 01 |
41000 |
SALARY CONTROL |
DEDUCTIONS |
4468 |
0 |
PENSION FUND 01 |
41420 |
PENSION FUND |
DEDUCTIONS |
0 |
4468 |
SPORTS CLUB |
41000 |
SALARY CONTROL |
DEDUCTIONS |
300 |
0 |
SPORTS CLUB |
43200 |
SPORTS CLUB |
DEDUCTIONS |
0 |
300 |
UNION FEES |
41000 |
SALARY CONTROL |
DEDUCTIONS |
2000 |
0 |
UNION FEES |
42800 |
UNION FEES |
DEDUCTIONS |
0 |
2000 |
FESTIVAL LOAN - ( CAPITAL ) |
32800 |
FESTIVAL LOANPAYABLE |
DEDUCTIONS |
0 |
16895.83 |
FESTIVAL LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
16895.83 |
0 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
31612.02 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
31612.02 |
0 |
S.S PREMIUM LOAN - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
14826.1 |
S.S PREMIUM LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
14826.1 |
0 |
SUNDRY LOAN 5 - ( CAPITAL ) |
32600 |
SUNDRY LOAN PAYABLE |
DEDUCTIONS |
0 |
40793.05 |
SUNDRY LOAN 5 - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
40793.05 |
0 |
EPF 12 % |
41200 |
EPF PAYABLE |
STATUTORY PAY |
0 |
117500 |
EPF 12 % |
76150 |
EPF EXPENCE |
STATUTORY PAY |
117500 |
0 |
ETF 3% |
41400 |
EPF PAYABLE |
STATUTORY PAY |
0 |
29375.01 |
ETF 3% |
76170 |
ETF EXPENCE |
STATUTORY PAY |
29375.01 |
0 |
GRATIUTY A/C |
42000 |
GRATUITY PAYABLE |
STATUTORY PAY |
0 |
90000 |
GRATIUTY A/C |
76190 |
GRATIUTY EXPENCE |
STATUTORY PAY |
90000 |
0 |
MEDICAL FUND A/C |
37540 |
MEDICAL FUND |
STATUTORY PAY |
0 |
9000 |
MEDICAL FUND A/C |
76230 |
MEDICAL FUND |
STATUTORY PAY |
9000 |
0 |
PAYE TAX |
41000 |
SALARY CONTROL |
DEDUCTIONS |
5213 |
0 |
PAYE TAX |
41100 |
PAYE PAYABLE |
DEDUCTIONS |
0 |
5213 |
CERESA EMP COOP BANK |
41000 |
SALARY CONTROL |
DEDUCTIONS |
1500 |
0 |
CERESA EMP COOP BANK |
44000 |
ACCRUDE CHARGE |
DEDUCTIONS |
0 |
1500 |
SALARY ADVANCE |
32810 |
SALARY ADVANCE |
DEDUCTIONS |
0 |
67500 |
SALARY ADVANCE |
41000 |
SALARY CONTROL |
DEDUCTIONS |
67500 |
0 |
EPF 8 % |
41000 |
SALARY CONTROL |
DEDUCTIONS |
142284 |
0 |
EPF 8 % |
41200 |
EPF PAYABLE |
DEDUCTIONS |
0 |
142284 |
Stamp Duty |
41000 |
SALARY CONTROL |
DEDUCTIONS |
825 |
0 |
Stamp Duty |
44200 |
STAMP DUTY |
DEDUCTIONS |
0 |
825 |
MEDICAL FUND MEM.CON |
41000 |
SALARY CONTROL |
DEDUCTIONS |
8500 |
0 |
MEDICAL FUND MEM.CON |
41800 |
MEDICAL FUND |
DEDUCTIONS |
0 |
8500 |
PENSION FUND 01 |
41000 |
SALARY CONTROL |
DEDUCTIONS |
8000 |
0 |
PENSION FUND 01 |
41420 |
PENSION FUND |
DEDUCTIONS |
0 |
8000 |
SPORTS CLUB |
41000 |
SALARY CONTROL |
DEDUCTIONS |
850 |
0 |
SPORTS CLUB |
43200 |
SPORTS CLUB |
DEDUCTIONS |
0 |
850 |
UNION FEES |
41000 |
SALARY CONTROL |
DEDUCTIONS |
5650 |
0 |
UNION FEES |
42800 |
UNION FEES |
DEDUCTIONS |
0 |
5650 |
FESTIVAL LOAN - ( CAPITAL ) |
32800 |
FESTIVAL LOANPAYABLE |
DEDUCTIONS |
0 |
28145.84 |
FESTIVAL LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
28145.84 |
0 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
42149.44 |
S.S INTERNATIONAL PREMIUM - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
42149.44 |
0 |
S.S PREMIUM LOAN - ( CAPITAL ) |
32500 |
S.S. PAYABLE |
DEDUCTIONS |
0 |
23745.8 |
S.S PREMIUM LOAN - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
23745.8 |
0 |
SUNDRY LOAN 5 - ( CAPITAL ) |
32600 |
SUNDRY LOAN PAYABLE |
DEDUCTIONS |
0 |
46996.25 |
SUNDRY LOAN 5 - ( CAPITAL ) |
41000 |
SALARY CONTROL |
DEDUCTIONS |
46996.25 |
0 |
EPF 12 % |
41200 |
EPF PAYABLE |
STATUTORY PAY |
0 |
213426 |
EPF 12 % |
76150 |
EPF EXPENCE |
STATUTORY PAY |
213426 |
0 |
ETF 3% |
41400 |
EPF PAYABLE |
STATUTORY PAY |
0 |
53356.5 |
ETF 3% |
76170 |
ETF EXPENCE |
STATUTORY PAY |
53356.5 |
0 |
GRATIUTY A/C |
42000 |
GRATUITY PAYABLE |
STATUTORY PAY |
0 |
159086 |
GRATIUTY A/C |
76190 |
GRATIUTY EXPENCE |
STATUTORY PAY |
159086 |
0 |
MEDICAL FUND A/C |
37540 |
MEDICAL FUND |
STATUTORY PAY |
0 |
25500 |
MEDICAL FUND A/C |
76230 |
MEDICAL FUND |
STATUTORY PAY |
25500 |
0 |
PAYE TAX |
41000 |
SALARY CONTROL |
DEDUCTIONS |
321 |
0 |
PAYE TAX |
41100 |
PAYE PAYABLE |
DEDUCTIONS |
0 |
321 |
CERESA EMP COOP BANK |
41000 |
SALARY CONTROL |
DEDUCTIONS |
3000 |
0 |
CERESA EMP COOP BANK |
44000 |
ACCRUDE CHARGE |
DEDUCTIONS |
0 |
3000 |
SALARY ADVANCE |
32810 |
SALARY ADVANCE |
DEDUCTIONS |
0 |
110400 |
SALARY ADVANCE |
41000 |
SALARY CONTROL |
DEDUCTIONS |
110400 |
0 |
Expressions written up to now
Expression for “DR”
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PAYE TAX'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'EPF 8 %'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'Stamp Duty'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'CERESA EMP COOP BANK'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'MEDICAL FUND MEM.CON'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PENSION FUND 01'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SALARY ADVANCE'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SPORTS CLUB'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'UNION FEES'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'FESTIVAL LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'S.S INTERNATIONAL PREMIUM - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'S.S PREMIUM LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'SUNDRY LOAN 5 - ( CAPITAL )'}>}[CR AMT])
++
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'EPF 12 %'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'ETF 3%'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'GRATIUTY A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'MEDICAL FUND A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'1'},DESCRIPTION={'PENSION-01 FUND COM. CONT.'}>}[DR AMT])
Expression for “CR”
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PAYE TAX'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'EPF 8 %'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'Stamp Duty'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'CERESA EMP COOP BANK'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'MEDICAL FUND MEM.CON'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PENSION FUND 01'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SALARY ADVANCE'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SPORTS CLUB'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'UNION FEES'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'FESTIVAL LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'S.S INTERNATIONAL PREMIUM - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'S.S PREMIUM LOAN - ( CAPITAL )'}>}[CR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'SUNDRY LOAN 5 - ( CAPITAL )'}>}[CR AMT])
++
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'EPF 12 %'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'ETF 3%'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'GRATIUTY A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'MEDICAL FUND A/C'}>}[DR AMT])
+
SUM({<[GL CODE TYPE]={'2'},DESCRIPTION={'PENSION-01 FUND COM. CONT.'}>}[DR AMT])
Please can anyone help me….
Hope all the details given….
Thank you.
MMARG83