Aggregations and group by

[andrea0901]

Hi I am attaching the excel file where I needed some help in qlik coding.

In the attached excel file in Data tab ->
1. I need to derive the calculations from column T based on S column till X column in qlik. I have done using aggregations in front end and it’s very slow.

2. In pivot tab , I need to calculate average of action duration for three pivot tables for three stages based on the filters mentioned in pivot sheet.

3. Only for cycle time we need to take maximum of closed time stamp for particlular invoice which was terminated at all stages i.e approval or assign approver.

Please help me in solving this it will be very helpful for me.

Thanks a lot.

[andrea0901]

Hi All,
Can anyone help me on this issue. Its little urgent.

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD IN#, 
     Workflow.companyCode, 
     Workflow.fiDocumentNumber, 
     Workflow.mmDocumentNumber, 
     Workflow.invoiceLineItemNumber, 
     Workflow.Instance_Type, 
     Workflow.blockType, 
     Workflow.Status, 
     Recon.INVOICE_LINEITEM_AMOUNT_EUR, 
     Recon.INVOICE_LINEITEM_AMOUNT, 
     Recon.CURRENCY, 
     Events.ACTION, 
     Events.ASSIGNED_TO_ROLE, 
     Events.ASSIGNED_TO_USER, 
     Events.ASSIGNED_TIMESTAMP, 
     Events.CLOSED_BY_USER, 
     Events.CLOSED_TIMESTAMP,
     Action_v2,
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber & '_' & Action_v2 as Workflow_Action_v2,
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber as [Workflow+item],
     Workflow.mmDocumentNumber & '_' & Workflow.invoiceLineItemNumber& '_' & Workflow.blockType as [Workflow item with workflow block],
     Events.CLOSED_TIMESTAMP - Events.ASSIGNED_TIMESTAMP as Duration,
     Country,
     Market,
     Region,
     Kernel,
     [Infosys Center],
     [Workflow ended]
FROM
[..\..\Downloads\Testing.xlsx]
(ooxml, embedded labels, table is Data);

Left Join (Table)
LOAD Workflow_Action_v2,
	 Sum(Duration) as [Action duration]
Resident Table
Group By Workflow_Action_v2;

Left Join (Table)
LOAD [Workflow+item],
	 Sum(Duration) as [Workflow duration]
Resident Table
Group By [Workflow+item];

You are doing vlookup for some of the field such as Action_v2. I didn't have the file from where you are doing this vlookup, so I picked it up directly from the Excel provided.

[andrea0901]

Hi , thank you I will do this.

[andrea0901]

Hi sunny,

Thanks for ur help.

I am finding a new challenge here. 

In my excel attached previuosly, there is a tab , PIVOT.

i am creating three scenarios. 

1st scenario:

For each scenario i need to harcode Workflow.Status as terminated and ACTION_V2 = Approver.

i wrote in straight table as

=Avg({<Workflow.Status = {'Terminated'}, Action_v2 = {'Approver'}>}[Action duration])

but i am not getting any values if i write like above.

similarly for second scenrio as well.

Please help me out.

[sunny_talwar]

I am not sure I follow, would you be able to show an image of what you get and point out what exactly isn't right?

[andrea0901]

Hi Sunny,

It is working now. Thanks for your help.