topic Re: What function is used to aggregate the row (column) in QlikView

What function is used to aggregate the row (column)

Wed, 25 Nov 2020 16:16:04 GMT

Excuse me !

What function is used to aggregate the row (column), for example

Returns the first [Date] of the [sales rate] of 0.8 ( note: Date is Variable)

Such as A001 : Return [2016-1-5]

A002 : Return [2016-1-4]

Returns the first [Date] of the [Inventory Turn] of 0.1 ( note: Date also is Variable)

Such as A001 : Return [2016-1-1]

A002 : Return [2016-1-2]

How to find out these “first [Date]”？？？？？

Re: What function is used to aggregate the row (column)

sunny_talwar — Wed, 10 Aug 2016 07:26:43 GMT

Where are you trying to get this information? In a chart? text box object?

Re: What function is used to aggregate the row (column)

Wed, 10 Aug 2016 07:53:51 GMT

Thanks Sunny T

Chart Type:

pivot table or Straight table

Re: What function is used to aggregate the row (column)

sunny_talwar — Wed, 10 Aug 2016 12:30:06 GMT

Would you be able to draw me a table here with the output you are looking to see?

Re: What function is used to aggregate the row (column)

Wed, 10 Aug 2016 12:46:40 GMT

Ok, Just a minute！

Re: What function is used to aggregate the row (column)

Anonymous — Wed, 10 Aug 2016 12:56:34 GMT

Do you need

dates accordingly the first maximum, second maximum.......... of sales rate ,

and

dates accordingly the first maximum, second maximum......... of Inventrory Turn

Re: What function is used to aggregate the row (column)

Wed, 10 Aug 2016 13:53:09 GMT

Re: What function is used to aggregate the row (column)

sunny_talwar — Wed, 10 Aug 2016 14:25:50 GMT

Can you attach the qvw file?

Uploading a Sample

Re: What function is used to aggregate the row (column)

Wed, 10 Aug 2016 15:58:46 GMT

Thanks Sunny T

Re: What function is used to aggregate the row (column)

sunny_talwar — Wed, 10 Aug 2016 19:34:19 GMT

Is this what you wanted?

Expressions used:

Min(Aggr(If(RangeSum(Above(Sum(Sales), 0, RowNo()))/Sum(TOTAL <Season> Sales) >= 0.80, SalesDate), Season, SalesDate))

Min(Aggr(If(RangeSum(Above(Sum(Sales), 0, RowNo()))/Sum(TOTAL <Season> Sales) >= 0.10, SalesDate), Season, SalesDate))

Not sure if this is your real data or not, but this may or may not work for your actual data because of the usage of Aggr() function. This worked for the sample because the data seems to be all well sorted, but if you are using QV 12 or above, you will be able to use The sortable Aggr function is finally here!‌ which might solve your problem in your real data (if it comes up).

Let me know if this is not what you were looking for.

Best,

Sunny

Re: What function is used to aggregate the row (column)

Thu, 11 Aug 2016 06:53:16 GMT

Hi Sunny T,

First ,Thank you very much for your kind help !

I used your Expressions to calculate ,but result is wrong.

Would you help me again!

The following questions

Re: What function is used to aggregate the row (column)

sunny_talwar — Thu, 11 Aug 2016 09:35:16 GMT

How is this accumulation related to your initial discussion of getting the dates in the table below? Would you be able to elaborate as to what were you expecting to see in that table?