Aggr Calculations

[#1]

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?

[MK_QSL]

Can you provide some sample data please?

[#3]

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

[MK_QSL]

Difficult to work without any sample data...

[jagan]

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.

[#6]

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

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

[ali_hijazi · Accepted solution]

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)

[jagan]

Hi,

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

Regards,

Jagan.

[#9]

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

[jagan]

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.