topic Re: sum problem in QlikView

sum problem

adiarnon — Tue, 10 Jun 2014 11:34:26 GMT

hi'

i have this table -

cust	date	hours
A	1.1.14	9
B	2.1.14	9
C	3.1.14	9
A	1.1.14	9
C	2.1.14	9

I WANT TO GET THIS -

CUST	HOURS
A	9
B	9
C	18

I WANT IT TO SUM FOR EACH CUST THE HOURS BUT ONLY FOR DISTINCT DATES.

LIKE- 'A' have two records with the date -1.1.14 so it need to count it only once...

hope its clear

TNX

Re: sum problem

anbu1984 — Tue, 10 Jun 2014 11:41:36 GMT

Do you have rows where cust and date are same and hours are different?

Re: sum problem

anbu1984 — Tue, 10 Jun 2014 11:44:50 GMT

Tab1:

Load distinct * Inline [

cust,date,hours

A,1.1.14,9

B,2.1.14,9

C,3.1.14,9

A,1.1.14,9

C,2.1.14,9 ];

Final:

Load cust,sum(hours)Resident Tab1 group by cust;

Re: sum problem

adiarnon — Tue, 10 Jun 2014 11:46:00 GMT

BAT I HAVE MORE COLUMN IN THIS TABLE AND IS THOSE COLUMNS THE DATA ARE DIFFERENT

Re: sum problem

anbu1984 — Tue, 10 Jun 2014 11:49:33 GMT

Tab1:

Load distinct cust,date,hours Inline [

cust,date,hours,col1,col2

A,1.1.14,9,1,2

B,2.1.14,9,11,22

C,3.1.14,9,22,33

A,1.1.14,9,44,55

C,2.1.14,9,66,77 ];

Final:

Load cust,sum(hours)Resident Tab1 group by cust;

Re: sum problem

adiarnon — Tue, 10 Jun 2014 11:54:41 GMT

I DONT WANT TO CREAT ANOTHER TABLE,

I WAND AN EXPRESSION THAT CAN MAKE IT.

SOMETHING LIKE:

SUM({DISTINCT DATE} HOURS) ?!?!?!?

Re: sum problem

Tue, 10 Jun 2014 11:56:45 GMT

can you post a sample app?

and capslock broken?

Re: sum problem

Tue, 10 Jun 2014 11:58:24 GMT

In Qlikview, it shows only distinct values in straight table.

Can you please share QVW.

Re: sum problem

Tue, 10 Jun 2014 12:10:18 GMT

Try this expression,

SUM({<DISTINCT DATE, CUST>} HOURS)

Re: sum problem

adiarnon — Tue, 10 Jun 2014 12:12:38 GMT

i cant share a sample because i dont have QV in the computer with the internet.

i will try to do it more clear:

i have this table -`

cust	date	Work	Available hours
A	1.1.14	2	9
B	2.1.14	3	9
C	3.1.14	4	9
A	1.1.14	5	9
C	2.1.14	6	9

I WANT TO GET THIS -

CUST	Available HOURS	Work Hours
A	9	7 (2+5)
B	9	3
C	18	10 (4+6)

i want in the available hours column to sum for each sust his available hours but not sum twise for the same date.

the work hours should just do - sum(work hours)

Re: sum problem

Tue, 10 Jun 2014 12:25:24 GMT

Check this ...

Re: Re: sum problem

anbu1984 — Tue, 10 Jun 2014 12:29:13 GMT

LIke this

Re: sum problem

er_mohit — Tue, 10 Jun 2014 12:36:42 GMT

see the attached file

Re: sum problem

adiarnon — Tue, 10 Jun 2014 13:10:08 GMT

i cant open the files =(

can someone explain in word the solution?

Re: sum problem

adiarnon — Tue, 10 Jun 2014 13:10:30 GMT

not working )=

Re: Re: sum problem

jerem1234 — Tue, 10 Jun 2014 13:11:13 GMT

Hello, I used the formula:

sum(aggr(firstsortedvalue(distinct hours, -hours), cust, date))

This will get the highest hours for a date (if they happen to be different for instance if you had A 1.1.14 to have 9 and 11 for hours), then sum all the hours for the different dates for a customer. I believe this is what you are looking for. Then work hours is:

sum(work_hours)

Please find attached.

Re: sum problem

anbu1984 — Tue, 10 Jun 2014 13:14:01 GMT

Script:

-------------------------------------------------------------

Tab1:

Load * Inline [

cust,date,avail_hours,work

A,1.1.14,9,2

B,2.1.14,9,3

C,3.1.14,9,4

A,1.1.14,9,5

C,2.1.14,9,6 ];

Final:

Load *,If(RowNo() <> 1 And cust = Previous(cust) And date = Previous(date),1,0) As Ind Resident Tab1 order by cust,date;

Drop table Tab1;

-------------------------------------------------------------

Use these expressions in front end

sum(If(Ind=0,avail_hours))

sum(work)