Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing a new Enhanced File Management feature in Qlik Cloud! GET THE DETAILS!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-05-25 12:50 PM

Aggregated Minimum value over 6 months

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

1,784 Views
0 Likes
19 Replies
Anonymous
Not applicable
‎2018-05-25 03:03 PM
Author

In response to sunny_talwar

This minimum value would changed based on the dimension

645 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-05-25 03:10 PM

In response to Anonymous

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

645 Views
0 Likes
Anonymous
Not applicable
‎2018-05-25 03:25 PM
Author

In response to sunny_talwar

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

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])))

645 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-05-26 08:31 PM

In response to Anonymous

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

645 Views
0 Likes
Anonymous
Not applicable
‎2018-05-31 11:02 AM
Author

In response to sunny_talwar

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/201712/1/2017 Total1/1/20181/1/2018 Total2/1/20182/1/2018 Total3/1/20183/1/2018 Total4/1/20184/1/2018 Total5/1/20185/1/2018 Total
LayerManagerNon-managerManagerNon-managerManagerNon-managerManagerNon-managerManagerNon-managerManagerNon-manager
2112112
3213156156
41781111213314314
5221121121781121311112
62351561561561111
733111111
Grand Total714215131851318414184141841317
645 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-05-31 11:20 AM

In response to Anonymous

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

645 Views
0 Likes
Anonymous
Not applicable
‎2018-05-31 02:11 PM
Author

In response to sunny_talwar

Sunny does this help?

     

DateActual DateEmployee IDNumber of LayerManager? Y = 1
December_FY1712/1/20171000071Non-manager
January_FY181/1/20181000072Non-manager
February_FY182/1/20181000072Non-manager
March_FY183/1/20181000072Non-manager
April_FY184/1/20181000072Non-manager
May_FY185/1/20181000072Non-manager
December_FY1712/1/20171000121Manager
January_FY181/1/20181000121Manager
February_FY182/1/20181000121Manager
March_FY183/1/20181000121Manager
April_FY184/1/20181000121Manager
May_FY185/1/20181000121Manager
December_FY1712/1/20171000142Non-manager
January_FY181/1/20181000142Non-manager
February_FY182/1/20181000142Non-manager
March_FY183/1/20181000142Non-manager
April_FY184/1/20181000142Non-manager
May_FY185/1/20181000142Non-manager
December_FY1712/1/20171000252Manager
January_FY181/1/20181000252Manager
February_FY182/1/20181000252Manager
March_FY183/1/20181000252Manager
April_FY184/1/20181000252Manager
May_FY185/1/20181000252Manager
December_FY1712/1/20171000276Non-manager
January_FY181/1/20181000277Non-manager
February_FY182/1/20181000277Non-manager
March_FY183/1/20181000277Non-manager
April_FY184/1/20181000277Non-manager
May_FY185/1/20181000277Non-manager
December_FY1712/1/20171000385Non-manager
January_FY181/1/20181000385Non-manager
February_FY182/1/20181000385Non-manager
March_FY183/1/20181000385Non-manager
April_FY184/1/20181000385Non-manager
May_FY185/1/20181000385Non-manager
December_FY1712/1/20171000457Non-manager
January_FY181/1/20181000458Non-manager
February_FY182/1/20181000458Non-manager
March_FY183/1/20181000458Non-manager
April_FY184/1/20181000458Non-manager
May_FY185/1/20181000458Non-manager
December_FY1712/1/20171000483Manager
January_FY181/1/20181000482Manager
February_FY182/1/20181000482Manager
March_FY183/1/20181000483Manager
April_FY184/1/20181000483Manager
May_FY185/1/20181000483Manager
December_FY1712/1/20171000538Non-manager
January_FY181/1/20181000538Non-manager
February_FY182/1/20181000538Non-manager
December_FY1712/1/20171000587Manager
January_FY181/1/20181000587Manager
February_FY182/1/20181000587Manager
March_FY183/1/20181000587Manager
April_FY184/1/20181000587Manager
May_FY185/1/20181000587Manager
December_FY1712/1/20171000766Non-manager
January_FY181/1/20181000767Non-manager
February_FY182/1/20181000767Non-manager
March_FY183/1/20181000767Non-manager
April_FY184/1/20181000767Non-manager
May_FY185/1/20181000767Non-manager
December_FY1712/1/20171000775Non-manager
January_FY181/1/20181000775Non-manager
February_FY182/1/20181000775Non-manager
March_FY183/1/20181000775Non-manager
April_FY184/1/20181000775Non-manager
May_FY185/1/20181000775Non-manager
December_FY1712/1/20171000786Non-manager
January_FY181/1/20181000787Non-manager
February_FY182/1/20181000787Non-manager
March_FY183/1/20181000787Non-manager
April_FY184/1/20181000787Non-manager
May_FY185/1/20181000787Non-manager
December_FY1712/1/20171000816Non-manager
January_FY181/1/20181000816Non-manager
February_FY182/1/20181000816Non-manager
March_FY183/1/20181000816Non-manager
April_FY184/1/20181000816Non-manager
May_FY185/1/20181000816Non-manager
December_FY1712/1/20171000828Non-manager
January_FY181/1/20181000828Non-manager
February_FY182/1/20181000828Non-manager
March_FY183/1/20181000828Non-manager
April_FY184/1/20181000828Non-manager
May_FY185/1/20181000828Non-manager
December_FY1712/1/20171000836Manager
January_FY181/1/20181000836Manager
February_FY182/1/20181000836Manager
March_FY183/1/20181000836Manager
April_FY184/1/20181000836Manager
May_FY185/1/20181000836Manager
December_FY1712/1/20171000905Non-manager
January_FY181/1/20181000905Non-manager
February_FY182/1/20181000905Non-manager
March_FY183/1/20181000905Non-manager
April_FY184/1/20181000905Non-manager
May_FY185/1/20181000905Non-manager
December_FY1712/1/20171001046Non-manager
January_FY181/1/20181001047Non-manager
February_FY182/1/20181001047Non-manager
March_FY183/1/20181001047Non-manager
April_FY184/1/20181001047Non-manager
May_FY185/1/20181001047Non-manager
December_FY1712/1/20171001055Non-manager
January_FY181/1/20181001055Non-manager
February_FY182/1/20181001055Non-manager
March_FY183/1/20181001055Non-manager
April_FY184/1/20181001055Non-manager
May_FY185/1/20181001055Non-manager
December_FY1712/1/20171001079Non-manager
January_FY181/1/20181001079Non-manager
February_FY182/1/20181001079Non-manager
March_FY183/1/20181001079Non-manager
April_FY184/1/20181001079Non-manager
May_FY185/1/20181001079Non-manager
December_FY1712/1/20171001187Non-manager
January_FY181/1/20181001186Non-manager
February_FY182/1/20181001186Non-manager
December_FY1712/1/20171001233Manager
January_FY181/1/20181001233Manager
February_FY182/1/20181001233Manager
March_FY183/1/20181001233Manager
April_FY184/1/20181001233Manager
May_FY185/1/20181001233Manager
December_FY1712/1/20171001246Non-manager
January_FY181/1/20181001246Non-manager
February_FY182/1/20181001246Non-manager
March_FY183/1/20181001246Non-manager
April_FY184/1/20181001246Non-manager
December_FY1712/1/20171001267Non-manager
January_FY181/1/20181001267Non-manager
February_FY182/1/20181001267Non-manager
March_FY183/1/20181001267Non-manager
April_FY184/1/20181001267Non-manager
May_FY185/1/20181001268Non-manager
December_FY1712/1/20171001275Non-manager
January_FY181/1/20181001275Non-manager
February_FY182/1/20181001275Non-manager
March_FY183/1/20181001275Non-manager
April_FY184/1/20181001275Non-manager
May_FY185/1/20181001275Non-manager
December_FY1712/1/20171001288Non-manager
January_FY181/1/20181001288Non-manager
February_FY182/1/20181001288Non-manager
March_FY183/1/20181001288Non-manager
April_FY184/1/20181001288Non-manager
May_FY185/1/20181001288Non-manager
December_FY1712/1/20171001318Non-manager
January_FY181/1/20181001318Non-manager
February_FY182/1/20181001318Non-manager
March_FY183/1/20181001318Non-manager
April_FY184/1/20181001318Non-manager
December_FY1712/1/20171001325Manager
January_FY181/1/20181001325Manager
February_FY182/1/20181001325Manager
March_FY183/1/20181001325Manager
April_FY184/1/20181001325Manager
May_FY185/1/20181001325Manager
December_FY1712/1/20171001356Non-manager
January_FY181/1/20181001356Non-manager
February_FY182/1/20181001356Non-manager
March_FY183/1/20181001356Non-manager
April_FY184/1/20181001356Non-manager
May_FY185/1/20181001356Non-manager
December_FY1712/1/20171001409Non-manager
January_FY181/1/20181001409Non-manager
February_FY182/1/20181001409Non-manager
March_FY183/1/20181001409Non-manager
April_FY184/1/20181001409Non-manager
May_FY185/1/20181001409Non-manager
December_FY1712/1/20171001485Manager
January_FY181/1/20181001485Manager
February_FY182/1/20181001485Manager
March_FY183/1/20181001485Manager
April_FY184/1/20181001485Manager
May_FY185/1/20181001485Manager
December_FY1712/1/20171001507Non-manager
January_FY181/1/20181001507Non-manager
February_FY182/1/20181001507Non-manager
March_FY183/1/20181001507Non-manager
April_FY184/1/20181001507Non-manager
May_FY185/1/20181001507Non-manager
December_FY1712/1/20171001566Manager
January_FY181/1/20181001566Manager
February_FY182/1/20181001566Manager
March_FY183/1/20181001566Manager
April_FY184/1/20181001566Manager
May_FY185/1/20181001566Manager
December_FY1712/1/20171001587Non-manager
January_FY181/1/20181001587Non-manager
February_FY182/1/20181001587Non-manager
March_FY183/1/20181001587Non-manager
April_FY184/1/20181001587Non-manager
May_FY185/1/20181001587Non-manager
December_FY1712/1/20171001598Non-manager
January_FY181/1/20181001598Non-manager
February_FY182/1/20181001598Non-manager
March_FY183/1/20181001598Non-manager
April_FY184/1/20181001598Non-manager
May_FY185/1/20181001598Non-manager
December_FY1712/1/20171001618Non-manager
January_FY181/1/20181001618Non-manager
February_FY182/1/20181001618Non-manager
March_FY183/1/20181001618Non-manager
April_FY184/1/20181001618Non-manager
May_FY185/1/20181001618Non-manager
December_FY1712/1/20171001666Non-manager
January_FY181/1/20181001667Non-manager
February_FY182/1/20181001667Non-manager
March_FY183/1/20181001667Non-manager
April_FY184/1/20181001667Non-manager
May_FY185/1/20181001667Non-manager
December_FY1712/1/20171001673Manager
January_FY181/1/20181001673Manager
February_FY182/1/20181001673Manager
March_FY183/1/20181001673Manager
April_FY184/1/20181001673Manager
May_FY185/1/20181001673Manager
December_FY1712/1/20171001749Non-manager
January_FY181/1/20181001749Non-manager
February_FY182/1/20181001749Non-manager
March_FY183/1/20181001749Non-manager
April_FY184/1/20181001749Non-manager
May_FY185/1/20181001749Non-manager
December_FY1712/1/20171001805Manager
January_FY181/1/20181001805Manager
February_FY182/1/20181001805Manager
March_FY183/1/20181001806Manager
April_FY184/1/20181001806Manager
May_FY185/1/20181001806Manager
December_FY1712/1/20171001827Manager
January_FY181/1/20181001827Manager
February_FY182/1/20181001827Manager
March_FY183/1/20181001827Manager
April_FY184/1/20181001827Manager
May_FY185/1/20181001827Manager
December_FY1712/1/20171001855Manager
645 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-05-31 08:34 PM

In response to Anonymous

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

302832.qvw
645 Views
0 Likes
Anonymous
Not applicable
‎2018-06-01 01:17 PM
Author

In response to sunny_talwar

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. 

645 Views
0 Likes
Anonymous
Not applicable
‎2018-07-09 03:37 PM
Author

In response to sunny_talwar

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?

645 Views
0 Likes