Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

manipulate a excel list with a sum formula

Hello everybody,

I have an Excel list:

Header 1Header 2
Illnes day2
Holiday day3

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 1Header 2
Illness days2
Holiday days3
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

8 Replies

Re: manipulate a excel list with a sum formula

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

Let me know

Not applicable

Re: manipulate a excel list with a sum formula

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

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

Not applicable

Re: manipulate a excel list with a sum formula

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.

chauhans85
Esteemed Contributor

Re: manipulate a excel list with a sum formula

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;

Not applicable

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

thank you.

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

Header 1Header 2
Illnesdays2
Holiday days3
Absencedays5

Can you help me!

Thanks

chauhans85
Esteemed Contributor

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

see the attched file

hope this helps

Not applicable

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

thank you.


Is there any possibility for this Format:

Header 1

Header 2
illness days2
Holiday days3
absence days (sum([illness days])+sum([Holiday days])5
all days360
public holiday10
working days (sum([public days])-sum([all days])350

presence in % 1-((sum([Absence days]))/ (sum([working days])))*100

98,7
Community Browser