topic Re: Count and group in QlikView

Count and group

stekol61 — Wed, 06 Feb 2019 12:39:12 GMT

Hi!

I have a pivot table where i count number of WO per incidents per week or month..

What i want is to group WO per incidents as below.
E.g '10' WO occurs '1' time in 2019-Jan

'5' WO occurs 3 times in July and 1 time in October.

Next step would be present the number of occurrence in a bar graph as below

How to do this?

Re: Count and group

qliksus — Thu, 07 Feb 2019 10:12:12 GMT

For the chart create just 4 expression and get the view

Exp1 = COUNT(distinct workorder) * ( COUNT(distinct workorder)=1 ) *-1

Exp2 = COUNT(distinct workorder) * ( COUNT(distinct workorder)=2 ) *-1

Exp3 = COUNT(distinct workorder) * ( COUNT(distinct workorder)>=3 and COUNT(distinct workorder)<5 ) *-1

Exp1 = Wx

Re: Count and group

stekol61 — Thu, 07 Feb 2019 11:31:19 GMT

Hi!

'Exp1 = Wx' what does this mean?

I tried your expressions but it doesn't work.

What i need is the sum of all '1' , sum of all '2' sum of all '3' etc. per month.

Actually the values seen in the pivot but without the 'wor_ParentTicketID' dimension

Re: Count and group

stekol61 — Thu, 07 Feb 2019 15:46:46 GMT

Hi!

A simpler explanation.

This pivot shows number of WO's per' Incident'.

In this example this specific 'Incident' has 4 WO's during August.

The calculation used is 'Count(WO)'

I want to see the same thing in a table or graph without using 'Incident' as a dimension

Re: Count and group

qliksus — Tue, 12 Feb 2019 04:22:09 GMT

How do you want to show if there are more than one incident for that month ?? i.e if you have data like the below how do you want the view to be ??

Wordeoder1 - 4

Workorder2-5

Re: Count and group

stekol61 — Tue, 12 Feb 2019 08:31:35 GMT

Hi!

In my table in the beginning of this post shows how it is presented i a table in Excel.

The left row shows the number of workorders per incident per month.

In the graph in can be presented as 1, 2, 3-5 and >5 (as in the graph above)

Re: Count and group

qliksus — Tue, 12 Feb 2019 08:42:43 GMT

Why don't you just create a chart with month as dimension and 4 expression like the below ??

1 = COUNT(distinct workorder) * ( COUNT(distinct workorder)=1 ) *-1

2 = COUNT(distinct workorder) * ( COUNT(distinct workorder)=2 ) *-1

3 = COUNT(distinct workorder) * ( COUNT(distinct workorder)>=3 and COUNT(distinct workorder)<5 ) *-1

>5 = COUNT(distinct workorder) * ( COUNT(distinct workorder)>=5 ) *-1

Here these 1,2,3,>5 are name of the expression

Re: Count and group

stekol61 — Tue, 12 Feb 2019 08:56:41 GMT

Doesn't work.
I think that 'Incident' needs to be in the expression.
I have used this formula and it almost works but it shows values that are to high.
Sum(Aggr(( COUNT( (WO))=3 ) *-1,Incident))