topic Re: Aggr with Above() function in QlikView

Aggr with Above() function

chriswain2 — Thu, 21 May 2015 08:03:46 GMT

I have created an expression which does a running maximum using above(), so if the values of the underlying expression within a dimension are 1,1,3,2,4,3 the output will be 1,1,3,3,4,4 This is it:

RangeMax(
      above(
                sum(
                      aggr(
                          ceil(RangeMax(0,((sum(Hours)/$(vEfficiency))/(sum([5 Day Hours Capacity]))))*count( DISTINCT Machine))
                     ,[Machine Class],[Week Year])
                )
      ,0,NoOfRows())
)
What I want to do is to use aggr to get an overall sum within each week for all machine classes calculated indicidually when I take away the dimension [Machine Class] from the straight table. I thought I could just put sum(aggr round the whole expression but it doesn't work. The above() part does not seem to work when I take away the dimension. Any ideas please?

Re: Aggr with Above() function

Gysbert_Wassenaar — Thu, 21 May 2015 16:35:09 GMT

If you remove the dimension then there are no rows so NoOfRows() has no value. Perhaps you can post a qlikview document that explains what you're trying to calculate.

Re: Aggr with Above() function

chriswain2 — Fri, 22 May 2015 07:18:38 GMT

Thanks Gysbert. The rows are on the [Week Year] field and I am only taking away the [Machine Class] dimension so there should still be rows. When I do that I want the same expression calculated separately but for each Machine Class. It works if I remove the Machine Class dimension and only select one Machine Class. So I thought I could do sum(aggr(....., [Machine Class]) but it does not give the correct answer.

I will try and post the qvw but I will have to get approval due to confidential information.

Re: Aggr with Above() function

Gysbert_Wassenaar — Fri, 22 May 2015 07:28:45 GMT

This should help you get permission: Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggr with Above() function

chriswain2 — Fri, 22 May 2015 07:32:34 GMT

Thanks, file attached. If it appears blank click CTRL SHIFT S. I want to take off the first dimension and have 11th May to be the sum of 7+3+9+....... = 92 etc