topic Re: Aggr Calculations in QlikView

Aggr Calculations

Wed, 19 Nov 2014 08:17:01 GMT

Hi All

I have a task at hand...

I have fields [Preparer] [Reviewer 1] [Reviewer 2] [Reviewer 3] .

I need to write a calculation that will show me all "prepered" documents that was reviewed more than once by the same Reviewer and the amount of times it was reviewed by that person.

How can I do this?

Re: Aggr Calculations

MK_QSL — Wed, 19 Nov 2014 08:20:08 GMT

Can you provide some sample data please?

Re: Aggr Calculations

Wed, 19 Nov 2014 08:27:22 GMT

Also there's a field for [Documents]. These are what is prepeared and reviewed.

So overall there's [Documents] [Preparer] [Reviewer 1] [Reviewer 2] [Reviewer 3]. Hope that will help

Re: Aggr Calculations

MK_QSL — Wed, 19 Nov 2014 08:30:29 GMT

Difficult to work without any sample data...

Re: Aggr Calculations

jagan — Wed, 19 Nov 2014 08:34:15 GMT

Hi,

It is difficult to come up with solution without some sample data. So attach some sample records and your expected output, this way you will get the solution easily and quickly.

Regards,

Jagan.

Re: Aggr Calculations

Wed, 19 Nov 2014 08:43:43 GMT

Sorry guys...how do I attach a document in my reply?

I can't seem to find a button that lets you attach documents.

Re: Aggr Calculations

ali_hijazi — Wed, 19 Nov 2014 08:46:32 GMT

you need to use CrossTable so that you the Reviewers are under one column Reviewer

Crosstable (Reviewer) load * resident your_table

this way you'll have a table like this

Preparer Reviwer

1 Reviwer1

1 Reviwer2

1 Reviwer1

....

then in your expression you'll need to write something like

count({<Reviewer={"=count(Reviwer)>1"}>}Preparer)

Re: Re: Aggr Calculations

jagan — Wed, 19 Nov 2014 09:00:22 GMT

Hi,

Click on Use Advanced Editor button and upload the file using attach button.

Regards,

Jagan.

Re: Aggr Calculations

Wed, 19 Nov 2014 09:01:25 GMT

Here is some sample data

Document	Preparer name	Reviewer 1	Reviewer 2	Reviewer 3
Obtain detailed listings and test accruals and other liabilities	Tumelo	Faan	Sean	None
Obtain detailed listings and test accruals and other liabilities	Tumelo	Faan	None	None
Obtain detailed listings and test accruals and other liabilities	Tumelo	Faan	None	None
Search for unrecorded liabilities	John	Tshepo	Lindsey	James
*Evaluate potential impact of non-compliance with laws and regulations	david	Thabo	Sarah	None
*Evaluate potential impact of non-compliance with laws and regulations	John	Thabo	None	None
*Obtain evidence on material related party transactions	Tebang	Lebo	Keri	Johny
*Respond to the risk of material misstatement due to fraud involving management override of controls	Justin	John	james	None

Re: Aggr Calculations

jagan — Wed, 19 Nov 2014 09:12:40 GMT

Hi,

First transform the data by using CrossTable

Crosstable (Reviewer, ReviewerName)

load * from your_table;

Now I need to write a calculation that will show me all "prepered" documents that was reviewed more than once by the same Reviewer and the amount of times it was reviewed by that person.

Chart : Straight Table

Dimension: Document, prepered, Reviewer

Expression: If(Count(Reviewer) >1, Count(Reviewer))

Hope this helps you.

Regards,

Jagan.