topic Re: Min and Max with Count in QlikView

Min and Max with Count

michealjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am newbie to Qlikview and facing issue in getting the result. Below is the sample data.

Org_Code, year

AA, 2014
AA, 2015
AA, 2015
AA, 2016
AA, 2017

BB, 2001
BB, 2019
BB, 2018
BB, 2001

CC, 2002
CC, 2003

I need the total count of org_code with max and min org_code.
For example, total count of AA is 5, BB is 4, CC is 2
The result I want is;

Expected Result:
Max Org_Code --> AA - 5
Min Org_code --> CC - 2

Ppease help on how to have the dimension and expression for it in qlikview

Taoufiq_Zarra — Thu, 19 Dec 2019 19:27:56 GMT

Hi,

Welcome to Qlik!
attached a qlikview file with the formulas or here they are

='Max Org_Code --> '&FirstSortedValue(Org_Code,-(Aggr(Count(year),Org_Code)))&'-'&Max(Aggr(Count(year),Org_Code))

='Max Org_Code --> '&FirstSortedValue(Org_Code,(Aggr(Count(year),Org_Code)))&'-'&Min(Aggr(Count(year),Org_Code))

Taoufiq_Zarra — Thu, 19 Dec 2019 19:38:25 GMT

sunny_talwar — Thu, 19 Dec 2019 19:40:04 GMT

🙂

michealjohn — Fri, 20 Dec 2019 11:22:22 GMT

thanks a lot for your help. Let me check on it and confirm you as I am having some issue in loading this .qvw file.

michealjohn — Fri, 20 Dec 2019 11:25:57 GMT

working fine as expected..thanks a lot for your help. Let me try to run with my data lake.. 🙂

Taoufiq_Zarra — Fri, 20 Dec 2019 11:32:13 GMT

Awesome, can you accept in this case the right solution 😁