topic Re: partial sum problem in QlikView

partial sum problem

Mon, 30 Dec 2013 19:35:22 GMT

Hello

sorry if Im asking something easy but Im very new to QW

I have this expresion an i cant get partial sum . What am I doing wrong ?

IF(findoc_series='1101'OR(findoc_series= '1106'),1 )

Thanks in advance

Re: partial sum problem

iktrayanov — Mon, 30 Dec 2013 23:01:28 GMT

It isn't clear what are you trying to do. Is this an expression or is this in your script? What are you trying to sum?

Re: partial sum problem

swuehl — Mon, 30 Dec 2013 23:09:08 GMT

Ionnis,

please read

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

You should use an aggregation function in your expression.

In the context of your partial sums, findoc_series is probably not unambiguous, so the expression can't evaluated to a value.

Are you looking for something like this?

=sum(if( findoc_series = 1101 or findoc_series = 1106, 1))

Re: partial sum problem

Tue, 31 Dec 2013 00:44:23 GMT

When i use this =sum(if( findoc_series = 1101 or findoc_series = 1106, 1)) i get

but this is not right for the column CUSTOMERS

when i use if( findoc_series = 1101 or findoc_series = 1106, 1) i get

this is correct for the number of customers by row but i dont have a Sum at the top

Re: partial sum problem

swuehl — Tue, 31 Dec 2013 01:00:19 GMT

It seems like you want a sum of rows in a pivot table, so maybe like

=sum( aggr( if( findoc_series = 1101 or findoc_series = 1106, 1), Dim1, Dim2, Dim3, Dim4))

Replace Dim1 ... Dim4 with your chart dimension fields.

Re: partial sum problem

iktrayanov — Tue, 31 Dec 2013 01:05:49 GMT

Change you total mode from expression default to sum

Re: partial sum problem

iktrayanov — Tue, 31 Dec 2013 01:08:11 GMT

Re: partial sum problem

Tue, 31 Dec 2013 01:16:11 GMT

This returns 0 to all fields

Re: partial sum problem

Tue, 31 Dec 2013 01:17:57 GMT

how can i do this ? the Total Mode is Expression Total by default and i have no choice

Re: partial sum problem

iktrayanov — Tue, 31 Dec 2013 01:23:54 GMT

Try this =count(if( findoc_series = 1101 or findoc_series = 1106, 1))

Re: partial sum problem

iktrayanov — Tue, 31 Dec 2013 01:34:36 GMT

To be able to change the total mode First change the your chart type to Straight Table go to the expressions tab change the total mode go back to general tab and change back to pivot table. Hope that helps.

Re: partial sum problem

Tue, 31 Dec 2013 01:49:03 GMT

same result as sum

I must make an explanation of the chart

'Time' is the time we have a sale

'SeriesType' is the type of Invoice

'Series No' is the No of the invoice

'Sale' is the Value of the Invoice

'Customer' in real is the rows of the Invoice='SeriesType . Thats why when we call sum we get number of rows

for example at the first img the first row of QV we have Time'9' , SeriesType(Invoice)'1101' Series No(Invoice No) '1657' Sale(Invoive value) 15€ and finaly CUSTOMER ( rows with differend items in the invoice) '2'

What we try to do is to convert Invoices to Customers 1 Invoice = 1 Customer . The target is to have how many customers per hour .

Re: partial sum problem

iktrayanov — Tue, 31 Dec 2013 02:01:53 GMT

Why don't you count the distinct invoices then.

Count(distinct [Series No])

Re: partial sum problem

Tue, 31 Dec 2013 02:09:26 GMT

Its a way but i still have to exclude some Series Type because they are not all Invoices , some of them are Delivery Notes ( type 2053) that is why i ask types 1101 & 1106 to count 1

Re: partial sum problem

jagan — Tue, 31 Dec 2013 02:11:03 GMT

Hi,

Try like this

=Count({< findoc_series = {'1101', '1106'>} distinct [Series No])

Regards,

Jagan.

Re: partial sum problem

Tue, 31 Dec 2013 02:22:33 GMT

Thats right

Thank you all guys

Yiannis

Greece