Hello all,

I refer you to this conversation http://community.qlik.com/message/164426#164426

I have found no solution so far. A prerequisite to a solution existing in Qlikview is the following :

Does Qlikview have an equivalent to the Excel formula arrays ?

I want to return for each Work order (referenced by WONUM) how many WONUM were reported before (where the reported date is REPORTDATE)

Not that here an aggregation is not possible since the date is a dimension and dynamic, so I have had no luck with set analysis.

 WONUM REPORTDATE WONUM created  before BC142529 2011/08/03 0 BC142796 2011/08/07 1 BC142808 2011/08/08 2 BC143523 2011/08/20 3 BC143531 2011/08/21 4 BC143679 2011/08/24 5

Where for instance C2 = {=SUM(IF((B2>B\$1:B\$7),1,0))} Formula array

I can't seem to find how to do this in Qlikview.

Thanks

Arthur

• ###### No equivalent to Excel Formula array in Qlikview ??

I am not sure if it will work for you but I think you can use the Accumulation funtcion for this.

Make a Count expression fe:

Count (DISTINCT (REPORTDATE))

And set the expression to Full Accumulation (left lower corner in the expression tab)

Hope this is usefull.

Good Luck,

Dennis.

• ###### Re: No equivalent to Excel Formula array in Qlikview ??

Hi Dennis and everybody

Thanks for your answer. I guess this would work, however in my case there might be 2 or more identical REPORTDATE.

Furthermore this is just a simplified version of what I need:

Simplified (this thread). For each WONUM, return the count of WONUM that have a REPORTDATE less or equal to the REPORTDATE of the WONUM in question.

Ultimately I need to calculate (refer to other thread, link above in first post):

For each WONUM, return the max FINISHDATE of the subset of WONUM that have a REPORTDATE less or equal to the WONUM in question

Hope you might have other ideas !

Arthur

• ###### Re: Can't Qlikview do Formula arrays ??

hi

not sure this is what you expect but look at th attached example

Regards

Gilles

• ###### Re: Can't Qlikview do Formula arrays ??

Hi,

I agree with Giles. You can use Range expressions. Also, QlikView behaves completely different compared to Excel. Here is the brief overiew of QV data structures and how it works when we load the data.

The data records are read into the memory, so that all the processing of data may be done through memory. I am sure you know this bit. QlikView treats all the data as Data Element Type (Columns / Fields) and Data Element Values (Values / Records). So each different data element value of each data element type is assigned a binary code and the data records are stored in binary-coded form and they are also sorted. By using the binary coding, very quick searches can be done on the tables. Also, QlikView removes the redundant information information and reduces the amount of data. However, the redundant information in stored as seperately with the frequencies for each unique data element value and across each data element type. When user makes a selection on data element values then the implied selection (possible values) are kept track seperately to present them to the user. By this process QlikView can perform rapid linear searches.

I understand this is not in detail but just a quick overview. I hope this helps!

Good luck!

Cheers - DV

• ###### Re: Can't Qlikview do Formula arrays ??

Hi guys, thanks so much for replying to my post.

I understand the range functions,but the result should be independant of the sorting:

For each WONUM, I want to return the number of WONUM which have a REPORTDATE less or equal to the REPORTDATE ofthis WONUM:

Range functions work fine like this:

 WONUM REPORTDATE WONUM reported  before BC142529 2011/08/03 0 BC142796 2011/08/07 1 BC142808 2011/08/08 2 BC143523 2011/08/20 3 BC143531 2011/08/21 4 BC143679 2011/08/24 5

but it should also work independently of the sorting (another/ no sorting):

 WONUM REPORTDATE WONUM reported  before explanation BC142796 2011/08/07 1 BC142529 has a REPORDATE before 2011/08/07 BC143679 2011/08/24 5 BC142796, BC142808, BC142529,  BC143531, BC143523  have a REPORDATE  before 2011/08/24 BC142808 2011/08/08 2 BC142796, BC143523  have a  REPORDATE before 2011/08/08 BC142529 2011/08/03 0 no WONUM have a REPORDATE before 2011/08/03 BC143531 2011/08/21 4 BC142796, BC142808, BC142529, BC143523   have a REPORDATE before 2011/08/21 BC143523 2011/08/20 3 BC142796, BC142808, BC142529,   have a REPORDATE before 2011/08/20

I hope this is clearer.

Ultimately (link in first post),I need to return, for every WONUM, the max FINISHDATE for the WONUM that have a REPORDATE before or equal to the REPORTDATE of that WONUM.

Granted Excel works differently, I was referring to array formulas in Excel if someone is familiar with them and would understand what I am trying to code e.g. C2 = {=SUM(IF((B2>B\$1:B\$7),1,0))}

Thanks a lot !

• ###### Re: Can't Qlikview do Formula arrays ??

can Qlikview do this or do I need to look at running the calculation externally to Qlikview ?

• ###### Re: Can't Qlikview do Formula arrays ??

Doing it on the front-end seems to be quite difficult, even with aggr and mostly because of the aggregation that requires date sorting.

I have instead, done a simple solution in the script and I'm getting the data that sums up correctly and that can also be sorted any way:

SOURCE:

REPORTDATE,

[WONUM created before]

FROM

[for_array.xls]

(biff, embedded labels, table is Sheet1\$);

//Did an extra resident load because I wouldn't get the sort by to work in the previous Load From, prob something stupid

SORTTEMP:

NoConcatenate

REPORTDATE,

[WONUM created before]

resident SOURCE

order by [REPORTDATE];

Drop Table SOURCE;

TABLE:

WONUM,

REPORTDATE,

RowNo() as [WONUM Count Prior and Inclusive],

RowNo()-1 as [WONUM Count Prior],

[WONUM created before]

Resident SORTTEMP;

Drop Table SORTTEMP;

Not sure if I misunderstood something...

• ###### Re: Can't Qlikview do Formula arrays ??

I couldn't access your original post, but maybe like attached?

Regards,

Stefan

• ###### Re: Can't Qlikview do Formula arrays ??

Hi Francois !