topic calculate amount in QlikView

calculate amount

arulsettu — Mon, 06 Apr 2015 04:12:32 GMT

i am calculating budget amount. i am having budget amount month wise like this

BD_JAN,

BD_FEB,

BD_MAR,

BD_APR,

BD_MAY,

BD_JUN,

BD_JUL,

BD_AUG,

BD_SEP,

BD_OCT,

BD_NOV,

BD_DEC

so i calculated like this

sum({<BD_TYPE={'GEN_ADMIN_EXP'}>}BD_JAN+BD_FEB+BD_MAR+BD_APR+BD_MAY+BD_JUN+BD_JUL+BD_AUG+BD_SEP+BD_OCT+BD_NOV+BD_DEC)

the issue is i tried to show this by month wise but the amount values are incorrect.

anyone suggest me how to calculate this amount fields together.

thanks

Re: calculate amount

Mon, 06 Apr 2015 04:21:01 GMT

hi,

you can use like this.

LOAD

BD_JAN,

1 as Month,

year //coming from table

Resident Table;

concatenate

LOAD

BD_FEB,

2 as Month,

year //coming from table

Resident Table;

concatenate

LOAD

BD_MAR,

3 as Month,

year //coming from table

Resident Table;

concatenate

LOAD

BD_APR,

4 as Month,

year //coming from table

Resident Table;

concatenate

---

concatenate

LOAD

BD_DEC,

12 as Month,

year //coming from table

Resident Table;

by this you will get month and year comming from your table.

then you put formula in expression.

Regards

Vimlesh

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 04:24:34 GMT

this BD_JAN,BD_FEB are not month fields. budget amount contains in the field.

ex: BD_JAN having jan month budget amount.

thanks

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 04:36:54 GMT

any ideas?

Re: calculate amount

ziadm — Mon, 06 Apr 2015 04:39:54 GMT

Use a Calender Table having Month Field in the Calendar and linking your budget table to your calender

to sum the budget for all months use set analysis

sum( {$<Month= >} Budget)

Re: calculate amount

ziadm — Mon, 06 Apr 2015 04:41:51 GMT

Creating A Master Calendar

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 04:45:42 GMT

Month coming from another table called trail_balance. i made link between two tables.

even no luck

thanks

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 05:03:10 GMT

please suggest some adivce

Re: calculate amount

jagan — Mon, 06 Apr 2015 05:26:48 GMT

Hi,

Try using CrossTable() and extract month name from the field name. If possible attach some sample data.

Regards,

Jagan.

Re: calculate amount

Mon, 06 Apr 2015 05:40:15 GMT

hi,

ya i know that so i have told you that creat a common amount with Month,

LOAD

BD_JAN as Amount,

1 as Month,

year //coming from table

Resident Table;

concatenate

LOAD

BD_FEB as Amount,

2 as Month,

year //coming from table

Resident Table;

concatenate

By this you will get month in a common field and when you will select month this will give you result according month. or if you will select year then it will give you a result in year.

Regards

Vimlesh

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 05:42:10 GMT

hi,

please find the attachment.

thanks

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 05:50:16 GMT

Hi Jagan ,

can you please check the app and tell me how to do it

thanks

Re: calculate amount

Mon, 06 Apr 2015 06:05:53 GMT

Hi,

try like this ,may be helpful you.

Regards

Vimlesh

Re: calculate amount

jagan — Mon, 06 Apr 2015 06:38:04 GMT

Hi,

Try this script

Temp:

CrossTable(Month, BudgetValue, 5)

LOAD %key, BD_TYPE, COMP_CODE, LOB_CODE, BD_YEAR, BD_APR, BD_AUG, BD_DEC, BD_FEB, BD_JAN, BD_JUL, BD_JUN, BD_MAR, BD_MAY, BD_NOV, BD_OCT, BD_SEP

FROM DataSource;

Budget:

LOAD

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Month,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

Resident Temp;

DROP TABLE Temp;

Regards,

jagan.

Re: calculate amount

arulsettu — Mon, 06 Apr 2015 07:16:35 GMT

hi jagan

it showing file name must be unique error. and COMP_CODE & LOB_CODE as %key this is for link.

Re: calculate amount

jagan — Mon, 06 Apr 2015 09:34:36 GMT

Hi,

Try this script

Temp:

CrossTable(Month, BudgetValue, 3)

LOAD COMP_CODE & LOB_CODE AS %key,

BD_TYPE, BD_YEAR, BD_APR, BD_AUG, BD_DEC, BD_FEB, BD_JAN, BD_JUL, BD_JUN, BD_MAR, BD_MAY, BD_NOV, BD_OCT, BD_SEP

FROM DataSource;

Budget:

LOAD

Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS MonthName,

MakeDate(BD_YEAR, Match(Capitalize(Right(Month, 3)), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

Resident Temp;

DROP TABLE Temp;