Need advice on advanced Qlikview calculations and limitations

[#1]

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.

Please help on this thread or the other one.

Thanks

Arthur

[Anonymous]

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.

[#3]

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

[#4]

hi

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

Regards

Gilles

[IAMDV]

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

[#6]

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 !

[#7]

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

[francoiswiid]

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:

LOAD WONUM,

     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

LOAD WONUM,

     REPORTDATE,

     [WONUM created before]

resident SOURCE

order by [REPORTDATE];

Drop Table SOURCE;

TABLE:

Load

          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...

[swuehl]

adiemunsch,

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

Regards,

Stefan

[#10]

Hi Francois !

Thanks very much for your answer, this works like a charm.

It is then an easy thing to have the count only for the current selection with:

[WONUM Count Prior] - FirstSortedValue(total [WONUM Count Prior],REPORTDATE)

Cheers !