Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
WillB
Partner
Partner

Calculate KPI based on multiple conditions

I have a problem where I want to tally Uploads based on a Status selected.  I want to find the latest Date where  the Uploads column has a value and then get that sum for all Customers.  I am able to accomplish that using the following:

sum(aggr(FirstSortedValue({1<Uploads={*}>}Uploads,-timestamp(Date, 'YYYY/M/DD hh:mm:ss')),hco_id))

I have a table of data as follows:

CustomerDateStatusUploads
11/1/19Registered0
12/1/19Uploaded1
13/1/19Submitted-
21/2/19Registered0
21/3/19Uploaded2
32/4/19Registered0
32/5/19Uploaded3
32/6/19Submitted-
32/7/19Withdrawn-

 

The issue is I don't want a Customer to be included if their latest status doesn't match with what I have selected. 

When I select a value of 'Submitted', then I get a correct sum of 4. The issue is when I select 'Uploaded'.  I get an incorrect sum of 6.  I don't want to include Customer 1 or Customer 3 because their most recent status is Submitted.  I should only include Customer 2 with an Upload value of 2.

Does anyone know how to get my list of customers where latest status is Uploaded and then find the latest Upload count for those customers?  I need to exclude the customers that are already in the 'Submitted' status.

I'm able to do this in a table, but getting this to work in a KPI has proven to be a different problem.  Additionally, for the time being, I'm not able to change the way my data is structured.

Any ideas on how to accomplish this?

Labels (4)
0 Replies