topic Re: manipulate a excel list with a sum formula in QlikView

manipulate a excel list with a sum formula

Fri, 22 Aug 2014 11:55:30 GMT

Hello everybody,

I have an Excel list:

Header 1	Header 2
Illnes day	2
Holiday day	3

but I would have an Excel list or anything that I can load up to my application

I Need a new calculated row with sum.

Header 1	Header 2
Illness days	2
Holiday days	3
Absence days sum([illnes days])+sum([Holiday days]))	5

Do you know, how can manipulate the list with a formula inside.

Thank you.

Jörn

Re: manipulate a excel list with a sum formula

alexandros17 — Fri, 22 Aug 2014 11:58:46 GMT

You can load the first excel file and create a chart with qlik that computes total

Let me know

Re: manipulate a excel list with a sum formula

Fri, 22 Aug 2014 12:59:24 GMT

I Need besides the sum formula nor a Division function.

Maybe can I load the Excel in a QVD File and after that I can manipulate the sum in the file.

Re: manipulate a excel list with a sum formula

MarcoWedel — Fri, 22 Aug 2014 23:00:29 GMT

Hallo Jörn,

kannst Du Deine Anforderungen bitte etwas präzisieren?

Wenn es nur um die Darstellung einer Summe geht, so ist der Vorschlag von Alessandro Saccone der Richtige.

Gruß

Marco

Re: manipulate a excel list with a sum formula

Mon, 25 Aug 2014 08:47:31 GMT

Ok, i will try my requirements more exactly.

I have table with illnes days and Holiday days as Excel List (see above, first table).

I want to have a list, which the list will include a calculated sum Formular of the two items Illness days and Holiday days and a calculated Division field of the two items. (see above, second table).

How I get the two forumula in the Excel List or maybe in qvw file, that i can upload one list with the calculated items.

Can anybody help me.

Thanks.

Re: manipulate a excel list with a sum formula

SunilChauhan — Mon, 25 Aug 2014 08:55:08 GMT

tab1:

lOAD * INLINE [

Header 1, Header 2

Illnes day, 2

Holiday day, 3

];

Load

sum(if([Header 1]='Illnes day',[Header 2]))+sum(if([Header 1]='Holiday day',[Header 2])) as [Absence days]

resident tab1 ;

drop table tab1;

Re: Re: manipulate a excel list with a sum formula

Mon, 25 Aug 2014 09:22:51 GMT

thank you.

How I get a table with the following Format, that i can get a diagramm table in the app view:

Header 1	Header 2
Illnesdays	2
Holiday days	3
Absencedays	5

Can you help me!

Thanks

Re: Re: Re: manipulate a excel list with a sum formula

SunilChauhan — Mon, 25 Aug 2014 09:53:42 GMT

see the attched file

hope this helps

Re: Re: Re: Re: manipulate a excel list with a sum formula

Mon, 25 Aug 2014 11:21:51 GMT

thank you.

Is there any possibility for this Format:

Header 1	Header 2
illness days	2
Holiday days	3
absence days (sum([illness days])+sum([Holiday days])	5
all days	360
public holiday	10
working days (sum([public days])-sum([all days])	350
*presence in % 1-((sum([Absence days]))/ (sum([working days])))100**	98,7