topic Re: sum in between in QlikView

sum in between

Anonymous — Wed, 20 Jan 2016 07:40:12 GMT

I have all these data in SQL in 1 table and i need to show in qlikview the sum of only 20 accounts..

what is the query to calculate the sum for accounts which start from 000-5302-001 to 000-5302-010 the accounts which start from 000-5303-008 to 000-5303

Account # Description

000-5301-003 Factory Plot Rent

000-5301-010 Amghara Plot Payroll Exp.

000-5302-001 Apartments Rent

000-5302-002 Medical Exp.

000-5302-003 Nutrition

000-5302-004 Buses Rent

000-5302-006 Telephones

000-5302-007 Factory Residence Charges

000-5302-008 Travelling Exp.

000-5302-010 Miscellaneous Exp. (Production)

000-5302-012 Cleaning

000-5302-013 Stationery

000-5302-014 Entertainment

000-5302-015 Crane Rents ( Raw Materials )

000-5302-024 Petty For Injured Labors

000-5302-026 Hotel Stay Exp.

000-5302-028 Trucks Diesel

000-5302-029 Diesel For Factory

000-5302-030 Crane Rent ( Scrap )

000-5302-031 Oxygen & Nitrogen

000-5302-032 Fine

000-5302-035 Clearance

000-5302-036 Camp Supplies & Maintenance

000-5302-055 Factory Tests Exp.

000-5302-058 Consultation Fees

000-5303-008 Plot Rent ( Amghara ) - 705

000-5303-009 New Plot Rent ( Amghara )-704

000-5303-012 Diesel - Amghara

000-5303-013 Water Amghra

000-5303-015 Office Amghra Exp

000-5303-019 New Plot Rent For Collecting Scrap (14,000M2)

000-5303-020 Demurrages

Re: sum in between

karthikeyan1504 — Wed, 20 Jan 2016 07:45:22 GMT

Hi Mohammad,

Please add one more column as rowno() in your load script as shown below,

Load rowno() as ID,

Account #,

Description

from table;

Then use if condition like count( if(id>0 and id<=10, Account#))

Warm Regards,

Karthikeyan.

Re: sum in between

jagan — Wed, 20 Jan 2016 07:55:32 GMT

Hi,

Try like this

LOAD

1 AS SpecialAccount

INLINE [

Account

000-5302-001

000-5302-002

000-5302-010

000-5303-008

000-5303-009

'];

Now use below expression

=Sum({<SpecialAccount={1}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: sum in between

karthikeyan1504 — Wed, 20 Jan 2016 07:59:57 GMT

We can have the inline table in excel sheet to manage it easily without touching the script in case if the account number needs to be changed.