Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count and Sum first time

Hi Experts

I need some help

I have a data set that has transactional data for sales orders and invoices. and would like to create a total, for the number of open sales order and a sum for the open sales orders. i could do this via SQL but would prefer to create the calculation in Qlikview.  help

Record SourceDocument Number
Item
LineTotal
Status
SALES ORDER1A10Open
INVOICE1A20Closed
SALES ORDER2B10Closed
SALES ORDER3C10Open
INVOICE2A10Open
Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count and Sum first time

Hi James,

please have a look at attached sample.

There are various methods to achieve what you want, it depends what you want to do with the total, where you want to display and if your total needs to be sensitive to user selections.

In the script, you could sum and count like

ResultTable:

LOAD

sum(LineTotal) as SumTotal, count([Document Number]) as CountTotal

Resident INPUT where Status='Open' and [Record Source] = 'SALES ORDER';

This total will not be sensitive to user selections in the front end.

In a chart object, you could use calculated dimensions like:

=if([Record Source]= 'SALES ORDER',[Record Source])

and

=if(Status='Open', Status)

and then use a plain sum(LineTotal) and count([Document Number]) as your two expressions (see attached, third table in the middle).

Or you could use [Record Source] and [Status] as dimensions, and use a limitation in your expressions, either using an if() statement (second table) or set analysis (first table). These expressions could also stand alone, without the dimensions, as shown in the text objects.

Hope this helps,

Stefan

2 Replies
MVP
MVP

Re: Count and Sum first time

Hi James,

please have a look at attached sample.

There are various methods to achieve what you want, it depends what you want to do with the total, where you want to display and if your total needs to be sensitive to user selections.

In the script, you could sum and count like

ResultTable:

LOAD

sum(LineTotal) as SumTotal, count([Document Number]) as CountTotal

Resident INPUT where Status='Open' and [Record Source] = 'SALES ORDER';

This total will not be sensitive to user selections in the front end.

In a chart object, you could use calculated dimensions like:

=if([Record Source]= 'SALES ORDER',[Record Source])

and

=if(Status='Open', Status)

and then use a plain sum(LineTotal) and count([Document Number]) as your two expressions (see attached, third table in the middle).

Or you could use [Record Source] and [Status] as dimensions, and use a limitation in your expressions, either using an if() statement (second table) or set analysis (first table). These expressions could also stand alone, without the dimensions, as shown in the text objects.

Hope this helps,

Stefan

Not applicable

Count and Sum first time

thanks swuehl was of great help.

Community Browser