topic Re: Aggregated Minimum value over 6 months in QlikView

Aggregated Minimum value over 6 months

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

I am trying to show the Minimum value in a field if a criteria is met aggregated by each month. Right now the minimum across the data set is what is pulling in my bar graph for each month, but I need those to be aggregated to change based off of the minimum for that month only.

I'm currenlty using the below to pull the minimum Layer number if the for all records that have "Manager" in as the criteria. This is currenlty showing me 4 for every month - however, May should have 5 since there is no one in that month that meets that criteria - but I'm still showing 4. Any help is greatly appreacited!

=Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 25 May 2018 17:21:35 GMT

Would you be able to share some raw data or app to show what you have...

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 17:30:41 GMT

Hi Sunny,

I did notice that I get the correct chart when I put the above formulat directly in the measure expresson versus creating the variable, and then calling the variable in the chart. Do you know why that may be? My Variable is named VMinLev and I'm calling it like =$(VMinLev) in the chart. When I do that I get 4 for every month, when I put the expression directly in as the measure I get 4,4,4,4,5 (which is correct)

I can't share any of the data but I could help describe fields if you ned.

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 25 May 2018 17:36:18 GMT

Are you saving the variable with an equal sign? Can you store the variable without the equal sign and see if that works?

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 17:37:47 GMT

I have a more complicated expression I'm ultimately trying to do and that's why I had created the variable to call and clean up the longer expression:

count({<[Rolling12_HC.Number of Layer]={">=$(VMinLev)"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

Do you know how I could incorporate Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1 into the if [Rolling12_HC.Number of Layer] is greater than the result?

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 17:49:52 GMT

Sunny,

It works not if I just call the variable in a chart without storing the equal sign. However, now when I call the variable in the more complex expression it doesn't work and pulls 0's.

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 25 May 2018 17:53:38 GMT

Pull it with an equal sign

count({<[Rolling12_HC.Number of Layer]={">=$(=VMinLev)"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

count({<[Rolling12_HC.Number of Layer]={">=$(=$(VMinLev))"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 18:17:16 GMT

Sunny,

Thank you so much! That did the trick! You saved me such a headache! Thank you!

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 18:56:06 GMT

Sunny,

Unfortunately still having issues with this formula. If I stop trying to call the variable and just try to type in a minimum criteria could you help me with why the below isn't working:

count({<[Rolling12_HC.Number of Layer]={">=$Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1"}>}[Rolling12_HC.Employee ID])/count(Distinct(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID])))

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 25 May 2018 19:02:49 GMT

Are you looking for a single min value or this min value needs to change based on dimension?

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 19:03:57 GMT

This minimum value would changed based on the dimension

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 25 May 2018 19:10:20 GMT

In that case, set analysis is not going to work.... set analysis is evaluated once per chart and will pick the min across all the dimensions.... You will need Aggr() function with if statement to get this done

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 25 May 2018 19:25:12 GMT

Sorry I'm very new at writing QS expressions - but if I wanted to do that - where would the aggr function go?

Re: Aggregated Minimum value over 6 months

sunny_talwar — Sun, 27 May 2018 00:31:58 GMT

This won't be straight forward... can you share some mocked up data to help you better?

Re: Aggregated Minimum value over 6 months

Anonymous — Thu, 31 May 2018 15:02:39 GMT

Hi Sunny,

here is a pivot of data that I want to use. Essentially I want to be able to look at each month as an aggregate, get the minimum "Layer" for Manager and add 1 to the minimum and I want to count everything that follows on any layer higher than the minimum. Each month that minimum changes though - so for december I would want to get the count for layers 4-7, January 3-7, February 3-7, March 5-7. My data has each month stacked in one.

	12/1/2017		12/1/2017 Total	1/1/2018		1/1/2018 Total	2/1/2018		2/1/2018 Total	3/1/2018		3/1/2018 Total	4/1/2018		4/1/2018 Total	5/1/2018		5/1/2018 Total
Layer	Manager	Non-manager		Manager	Non-manager		Manager	Non-manager		Manager	Non-manager		Manager	Non-manager		Manager	Non-manager
2				1	1	2	1	1	2
3	2	1	3	1	5	6	1	5	6
4	1	7	8	1		1	1		1	2	1	3	3	1	4	3	1	4
5	2		2	1	1	2	1	1	2	1	7	8	1	12	13	1	11	12
6	2	3	5	1	5	6	1	5	6	1	5	6		1	1		1	1
7		3	3		1	1		1	1		1	1
Grand Total	7	14	21	5	13	18	5	13	18	4	14	18	4	14	18	4	13	17

Re: Aggregated Minimum value over 6 months

sunny_talwar — Thu, 31 May 2018 15:20:58 GMT

You are showing me the output you expect, but I don't have the input to work with

Re: Aggregated Minimum value over 6 months

Anonymous — Thu, 31 May 2018 18:11:16 GMT

Sunny does this help?

Date	Actual Date	Employee ID	Number of Layer	Manager? Y = 1
December_FY17	12/1/2017	100007	1	Non-manager
January_FY18	1/1/2018	100007	2	Non-manager
February_FY18	2/1/2018	100007	2	Non-manager
March_FY18	3/1/2018	100007	2	Non-manager
April_FY18	4/1/2018	100007	2	Non-manager
May_FY18	5/1/2018	100007	2	Non-manager
December_FY17	12/1/2017	100012	1	Manager
January_FY18	1/1/2018	100012	1	Manager
February_FY18	2/1/2018	100012	1	Manager
March_FY18	3/1/2018	100012	1	Manager
April_FY18	4/1/2018	100012	1	Manager
May_FY18	5/1/2018	100012	1	Manager
December_FY17	12/1/2017	100014	2	Non-manager
January_FY18	1/1/2018	100014	2	Non-manager
February_FY18	2/1/2018	100014	2	Non-manager
March_FY18	3/1/2018	100014	2	Non-manager
April_FY18	4/1/2018	100014	2	Non-manager
May_FY18	5/1/2018	100014	2	Non-manager
December_FY17	12/1/2017	100025	2	Manager
January_FY18	1/1/2018	100025	2	Manager
February_FY18	2/1/2018	100025	2	Manager
March_FY18	3/1/2018	100025	2	Manager
April_FY18	4/1/2018	100025	2	Manager
May_FY18	5/1/2018	100025	2	Manager
December_FY17	12/1/2017	100027	6	Non-manager
January_FY18	1/1/2018	100027	7	Non-manager
February_FY18	2/1/2018	100027	7	Non-manager
March_FY18	3/1/2018	100027	7	Non-manager
April_FY18	4/1/2018	100027	7	Non-manager
May_FY18	5/1/2018	100027	7	Non-manager
December_FY17	12/1/2017	100038	5	Non-manager
January_FY18	1/1/2018	100038	5	Non-manager
February_FY18	2/1/2018	100038	5	Non-manager
March_FY18	3/1/2018	100038	5	Non-manager
April_FY18	4/1/2018	100038	5	Non-manager
May_FY18	5/1/2018	100038	5	Non-manager
December_FY17	12/1/2017	100045	7	Non-manager
January_FY18	1/1/2018	100045	8	Non-manager
February_FY18	2/1/2018	100045	8	Non-manager
March_FY18	3/1/2018	100045	8	Non-manager
April_FY18	4/1/2018	100045	8	Non-manager
May_FY18	5/1/2018	100045	8	Non-manager
December_FY17	12/1/2017	100048	3	Manager
January_FY18	1/1/2018	100048	2	Manager
February_FY18	2/1/2018	100048	2	Manager
March_FY18	3/1/2018	100048	3	Manager
April_FY18	4/1/2018	100048	3	Manager
May_FY18	5/1/2018	100048	3	Manager
December_FY17	12/1/2017	100053	8	Non-manager
January_FY18	1/1/2018	100053	8	Non-manager
February_FY18	2/1/2018	100053	8	Non-manager
December_FY17	12/1/2017	100058	7	Manager
January_FY18	1/1/2018	100058	7	Manager
February_FY18	2/1/2018	100058	7	Manager
March_FY18	3/1/2018	100058	7	Manager
April_FY18	4/1/2018	100058	7	Manager
May_FY18	5/1/2018	100058	7	Manager
December_FY17	12/1/2017	100076	6	Non-manager
January_FY18	1/1/2018	100076	7	Non-manager
February_FY18	2/1/2018	100076	7	Non-manager
March_FY18	3/1/2018	100076	7	Non-manager
April_FY18	4/1/2018	100076	7	Non-manager
May_FY18	5/1/2018	100076	7	Non-manager
December_FY17	12/1/2017	100077	5	Non-manager
January_FY18	1/1/2018	100077	5	Non-manager
February_FY18	2/1/2018	100077	5	Non-manager
March_FY18	3/1/2018	100077	5	Non-manager
April_FY18	4/1/2018	100077	5	Non-manager
May_FY18	5/1/2018	100077	5	Non-manager
December_FY17	12/1/2017	100078	6	Non-manager
January_FY18	1/1/2018	100078	7	Non-manager
February_FY18	2/1/2018	100078	7	Non-manager
March_FY18	3/1/2018	100078	7	Non-manager
April_FY18	4/1/2018	100078	7	Non-manager
May_FY18	5/1/2018	100078	7	Non-manager
December_FY17	12/1/2017	100081	6	Non-manager
January_FY18	1/1/2018	100081	6	Non-manager
February_FY18	2/1/2018	100081	6	Non-manager
March_FY18	3/1/2018	100081	6	Non-manager
April_FY18	4/1/2018	100081	6	Non-manager
May_FY18	5/1/2018	100081	6	Non-manager
December_FY17	12/1/2017	100082	8	Non-manager
January_FY18	1/1/2018	100082	8	Non-manager
February_FY18	2/1/2018	100082	8	Non-manager
March_FY18	3/1/2018	100082	8	Non-manager
April_FY18	4/1/2018	100082	8	Non-manager
May_FY18	5/1/2018	100082	8	Non-manager
December_FY17	12/1/2017	100083	6	Manager
January_FY18	1/1/2018	100083	6	Manager
February_FY18	2/1/2018	100083	6	Manager
March_FY18	3/1/2018	100083	6	Manager
April_FY18	4/1/2018	100083	6	Manager
May_FY18	5/1/2018	100083	6	Manager
December_FY17	12/1/2017	100090	5	Non-manager
January_FY18	1/1/2018	100090	5	Non-manager
February_FY18	2/1/2018	100090	5	Non-manager
March_FY18	3/1/2018	100090	5	Non-manager
April_FY18	4/1/2018	100090	5	Non-manager
May_FY18	5/1/2018	100090	5	Non-manager
December_FY17	12/1/2017	100104	6	Non-manager
January_FY18	1/1/2018	100104	7	Non-manager
February_FY18	2/1/2018	100104	7	Non-manager
March_FY18	3/1/2018	100104	7	Non-manager
April_FY18	4/1/2018	100104	7	Non-manager
May_FY18	5/1/2018	100104	7	Non-manager
December_FY17	12/1/2017	100105	5	Non-manager
January_FY18	1/1/2018	100105	5	Non-manager
February_FY18	2/1/2018	100105	5	Non-manager
March_FY18	3/1/2018	100105	5	Non-manager
April_FY18	4/1/2018	100105	5	Non-manager
May_FY18	5/1/2018	100105	5	Non-manager
December_FY17	12/1/2017	100107	9	Non-manager
January_FY18	1/1/2018	100107	9	Non-manager
February_FY18	2/1/2018	100107	9	Non-manager
March_FY18	3/1/2018	100107	9	Non-manager
April_FY18	4/1/2018	100107	9	Non-manager
May_FY18	5/1/2018	100107	9	Non-manager
December_FY17	12/1/2017	100118	7	Non-manager
January_FY18	1/1/2018	100118	6	Non-manager
February_FY18	2/1/2018	100118	6	Non-manager
December_FY17	12/1/2017	100123	3	Manager
January_FY18	1/1/2018	100123	3	Manager
February_FY18	2/1/2018	100123	3	Manager
March_FY18	3/1/2018	100123	3	Manager
April_FY18	4/1/2018	100123	3	Manager
May_FY18	5/1/2018	100123	3	Manager
December_FY17	12/1/2017	100124	6	Non-manager
January_FY18	1/1/2018	100124	6	Non-manager
February_FY18	2/1/2018	100124	6	Non-manager
March_FY18	3/1/2018	100124	6	Non-manager
April_FY18	4/1/2018	100124	6	Non-manager
December_FY17	12/1/2017	100126	7	Non-manager
January_FY18	1/1/2018	100126	7	Non-manager
February_FY18	2/1/2018	100126	7	Non-manager
March_FY18	3/1/2018	100126	7	Non-manager
April_FY18	4/1/2018	100126	7	Non-manager
May_FY18	5/1/2018	100126	8	Non-manager
December_FY17	12/1/2017	100127	5	Non-manager
January_FY18	1/1/2018	100127	5	Non-manager
February_FY18	2/1/2018	100127	5	Non-manager
March_FY18	3/1/2018	100127	5	Non-manager
April_FY18	4/1/2018	100127	5	Non-manager
May_FY18	5/1/2018	100127	5	Non-manager
December_FY17	12/1/2017	100128	8	Non-manager
January_FY18	1/1/2018	100128	8	Non-manager
February_FY18	2/1/2018	100128	8	Non-manager
March_FY18	3/1/2018	100128	8	Non-manager
April_FY18	4/1/2018	100128	8	Non-manager
May_FY18	5/1/2018	100128	8	Non-manager
December_FY17	12/1/2017	100131	8	Non-manager
January_FY18	1/1/2018	100131	8	Non-manager
February_FY18	2/1/2018	100131	8	Non-manager
March_FY18	3/1/2018	100131	8	Non-manager
April_FY18	4/1/2018	100131	8	Non-manager
December_FY17	12/1/2017	100132	5	Manager
January_FY18	1/1/2018	100132	5	Manager
February_FY18	2/1/2018	100132	5	Manager
March_FY18	3/1/2018	100132	5	Manager
April_FY18	4/1/2018	100132	5	Manager
May_FY18	5/1/2018	100132	5	Manager
December_FY17	12/1/2017	100135	6	Non-manager
January_FY18	1/1/2018	100135	6	Non-manager
February_FY18	2/1/2018	100135	6	Non-manager
March_FY18	3/1/2018	100135	6	Non-manager
April_FY18	4/1/2018	100135	6	Non-manager
May_FY18	5/1/2018	100135	6	Non-manager
December_FY17	12/1/2017	100140	9	Non-manager
January_FY18	1/1/2018	100140	9	Non-manager
February_FY18	2/1/2018	100140	9	Non-manager
March_FY18	3/1/2018	100140	9	Non-manager
April_FY18	4/1/2018	100140	9	Non-manager
May_FY18	5/1/2018	100140	9	Non-manager
December_FY17	12/1/2017	100148	5	Manager
January_FY18	1/1/2018	100148	5	Manager
February_FY18	2/1/2018	100148	5	Manager
March_FY18	3/1/2018	100148	5	Manager
April_FY18	4/1/2018	100148	5	Manager
May_FY18	5/1/2018	100148	5	Manager
December_FY17	12/1/2017	100150	7	Non-manager
January_FY18	1/1/2018	100150	7	Non-manager
February_FY18	2/1/2018	100150	7	Non-manager
March_FY18	3/1/2018	100150	7	Non-manager
April_FY18	4/1/2018	100150	7	Non-manager
May_FY18	5/1/2018	100150	7	Non-manager
December_FY17	12/1/2017	100156	6	Manager
January_FY18	1/1/2018	100156	6	Manager
February_FY18	2/1/2018	100156	6	Manager
March_FY18	3/1/2018	100156	6	Manager
April_FY18	4/1/2018	100156	6	Manager
May_FY18	5/1/2018	100156	6	Manager
December_FY17	12/1/2017	100158	7	Non-manager
January_FY18	1/1/2018	100158	7	Non-manager
February_FY18	2/1/2018	100158	7	Non-manager
March_FY18	3/1/2018	100158	7	Non-manager
April_FY18	4/1/2018	100158	7	Non-manager
May_FY18	5/1/2018	100158	7	Non-manager
December_FY17	12/1/2017	100159	8	Non-manager
January_FY18	1/1/2018	100159	8	Non-manager
February_FY18	2/1/2018	100159	8	Non-manager
March_FY18	3/1/2018	100159	8	Non-manager
April_FY18	4/1/2018	100159	8	Non-manager
May_FY18	5/1/2018	100159	8	Non-manager
December_FY17	12/1/2017	100161	8	Non-manager
January_FY18	1/1/2018	100161	8	Non-manager
February_FY18	2/1/2018	100161	8	Non-manager
March_FY18	3/1/2018	100161	8	Non-manager
April_FY18	4/1/2018	100161	8	Non-manager
May_FY18	5/1/2018	100161	8	Non-manager
December_FY17	12/1/2017	100166	6	Non-manager
January_FY18	1/1/2018	100166	7	Non-manager
February_FY18	2/1/2018	100166	7	Non-manager
March_FY18	3/1/2018	100166	7	Non-manager
April_FY18	4/1/2018	100166	7	Non-manager
May_FY18	5/1/2018	100166	7	Non-manager
December_FY17	12/1/2017	100167	3	Manager
January_FY18	1/1/2018	100167	3	Manager
February_FY18	2/1/2018	100167	3	Manager
March_FY18	3/1/2018	100167	3	Manager
April_FY18	4/1/2018	100167	3	Manager
May_FY18	5/1/2018	100167	3	Manager
December_FY17	12/1/2017	100174	9	Non-manager
January_FY18	1/1/2018	100174	9	Non-manager
February_FY18	2/1/2018	100174	9	Non-manager
March_FY18	3/1/2018	100174	9	Non-manager
April_FY18	4/1/2018	100174	9	Non-manager
May_FY18	5/1/2018	100174	9	Non-manager
December_FY17	12/1/2017	100180	5	Manager
January_FY18	1/1/2018	100180	5	Manager
February_FY18	2/1/2018	100180	5	Manager
March_FY18	3/1/2018	100180	6	Manager
April_FY18	4/1/2018	100180	6	Manager
May_FY18	5/1/2018	100180	6	Manager
December_FY17	12/1/2017	100182	7	Manager
January_FY18	1/1/2018	100182	7	Manager
February_FY18	2/1/2018	100182	7	Manager
March_FY18	3/1/2018	100182	7	Manager
April_FY18	4/1/2018	100182	7	Manager
May_FY18	5/1/2018	100182	7	Manager
December_FY17	12/1/2017	100185	5	Manager

Re: Aggregated Minimum value over 6 months

sunny_talwar — Fri, 01 Jun 2018 00:34:44 GMT

Okay so, I loaded the data into a QlikView dashboard and I am trying to understand where are you calculating =Min(if([Manager? Y = 1]='Manager',[Number of Layer]))+1 this? What are the chart dimensions for this minimum? I would need some directions as to what you need? May be an example from this data to help me understand the results you need

Re: Aggregated Minimum value over 6 months

Anonymous — Fri, 01 Jun 2018 17:17:20 GMT

Sunny,

this is a field that is pulled in from the excel file already calculated. I'm not calculating the Number of Layer in QS. It's a stagnent value that comes in with the employees record.

Re: Aggregated Minimum value over 6 months

Anonymous — Mon, 09 Jul 2018 19:37:45 GMT

Hi Sunny,

I know it's been a while since I asked about this issue - but I still haven't figured it out. Nothing I'm doing is working in the chart. I can pull the variable alone over the date time frame and the minimum updates right, but when I try to put it in an expression it sets the minimum for the total data set regardless of what the minimum was for that specific month. Any new thoughts on how to tackle this?