34 Replies Latest reply: Feb 25, 2012 8:14 AM by swuehl

How to have the Max RowNo from an expression available for all rows?

The below provides the RowNo for the latest retrieved value in the system.

// expression 1:

= if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)

My impression is that it would be simple to have that RowNo available on all rows by wrapping the above expression in MAX, it does not work.

// expression 2:

= MAX(   if(max(retrieved) = max(TOTAL retrieved), RowNo(),0)   )

Does anyone see what I am doing wrong in this very simple expression?

Simply need to be able to have that max RowNo from the first expression available on every for another expression.

Thanks for any assistance....

D

• How to have the Max RowNo from an expression available for all rows?

You can't just embed one aggregation funcion into another (max() into max() ) like this.

You would need to use advanced aggregation, using a dimension list for the aggr() function.

Honestly, I haven't fully understood your issue, could you post a sample?

• How to have the Max RowNo from an expression available for all rows?

Swuehl, can you please repost your message.  I removed one of my messages and it took yours as well. thanks....

• How to have the Max RowNo from an expression available for all rows?

Will try...

The expression could look like

=max(total aggr(NODISTINCT if(max(TOTAL retrieved) = max(retrieved), RowNo(),0), YrWk))

• Re: How to have the Max RowNo from an expression available for all rows?

How about this, adding in use of Set Analysis which basically does the calc regardless of the selection filter but honors the dimensions:

=Max({1} total aggr(NODISTINCT if(max({1}TOTAL retrieved) = max({1} retrieved), RowNo(),0) ,YrWk))

• How to have the Max RowNo from an expression available for all rows?

Maybe, I also tried with clearing the selection one can make in the selection multi box.

If you use an expression with set identifier 1 as you did in your posted expression, I believe you will always get the '46' max rownumber regardless of selections.

But if  you select Week(s) from your multibox, your future row v maybe e.g. only 30, so the expression doesn't seem right to me.

Honestly, I haven't fully understood what you are doing and your table looks too complex to get a quick insight.

Maybe you could work with a set identifier {1} or by clearing the four field you can select in your multibox, like

{<component=, subcomponent=,cf_rpn=,retrieved=>}

- maybe not.

--

Stefan

• How to have the Max RowNo from an expression available for all rows?

Stefan,

I must be missing something, doing a very similar expression as before  but getting issue, you see what is incorrect?

This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

=if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

This expression that makes that number available on all rows gives an incorrect answer of 36, you see what I am diong wrong:

=Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )

• How to have the Max RowNo from an expression available for all rows?

Getting closer.

I believe aggr() function will sort the given dimensions by load order, and you can't change that behaviour.

Your load order of field YrWk seems not to be numerical asc or text asc. So your second expression will have a different row number representing value '2012-5' then your table (which is sorted properly). If you sort your table dimension by load order, you will see the expression will match, but the overall outcome is not what you want.

Try using an appropriate load order?

Regards,

Stefan

• How to have the Max RowNo from an expression available for all rows?

Stefan,

Thanks... I added an ID column and sorted the table in excel  by the ID column, but no help.  Must be I have to sort the excel file on the way into Qlikview, is that what you are saying?  Is that as easy as adding an ASC to the load by chance?

Thanks,

Don

• How to have the Max RowNo from an expression available for all rows?

Not sure what you want to do with excel, is Excel your data source? I currently don't have access to the last version of your qvw-file, so I can't look at the load statement.

The load order of field YrWk is determined by the order your distinct values are read into your data model, this could be done using multiple loads, concatenation etc.

You could try creating an autogenerated table with field YrWk created for the range you need upfront, sorted ascending, then load your other tables in, then I think you can drop your initially created table again.

Or alternatively take care that the field values are read in sorted from your table source(s).

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

The data source is excel currently for all the data but may change.

The data source for the dates dimension is excel, added on so could do the projection and have the future dates that are not in the current data.

Attached is the file that has the dates dimension I mentioned, look at the YYYYWW tab in excel.

The Qlikview load of that excel table is:

IdVal,

YrWk

FROM

C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

(ooxml, embedded labels, table is YYYYWW);

Note, I am not sure how to do the autogenerated item you mentioned, so created this table in excel to move forward.

Can you see what I am doing?

Thanks,

Don

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

I have tried a few combinations of trying to sort in Qlikview, does not see to do the trick.

If I change my import for this table to following, all still ok.  This table joins the existing synthetic join table that was given to me:

BurnDownDates:

IdVal,

YrWk

FROM

C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

(ooxml, embedded labels, table is YYYYWW);

If I then try versions to sort this data in qlikview on load, DOES NOT  work.  What I mean by not working is there is no table joined to the existing synthetic table any longer with the future dates and the future dates disappear.  Ideas???

BurnDownDates:

IdVal,

YrWk

FROM

C:\Users\doschafe\Documents\Qlikview\DATA\Error_Burn_Down_spec3_Open.xlsx

(ooxml, embedded labels, table is YYYYWW);

YYYYWW:

IdVal,

YrWk

resident BurnDownDates

order by IdVal;

Drop Table BurnDownDates;

• Re: How to have the Max RowNo from an expression available for all rows?

Ok, have not played with resident files and Excel and sorting.  Played with loading excel files, inline data, using resident tables and sorting a bit on extremely small file to sort out the quirks.  Think i have the kinks worked out in a test file, now will try on the large file.  Here is what I did:

// Excel Load, Sort Resident, Drop Original

BurnDownDates:

ColA,

ColB

FROM

U:\Qlikview_CURR\Error_Burn_Down_spec3_Open.xlsx

(ooxml, embedded labels, table is YYYYWW);

SortedBDD:

ColA As IdVal,

ColB As YrWk

resident BurnDownDates

order by ColA ASC;

// order by ColA DESC;

drop table BurnDownDates;

/*

// Inline Load, Sort Resident, Drop Original

BurnDownDates:

[

A1, A2

1, 2011-37

2, 2011-38

3, 2011-39

4, 2011-40

5, 2011-41

6, 2011-42

7, 2011-43

8, 2011-44

9, 2011-45

10, 2011-46

11, 2011-47

12, 2011-48

13, 2011-49

14, 2011-50

15, 2011-51

16, 2011-52

17, 2012-1

18, 2012-2

19, 2012-3

20, 2012-4

21, 2012-5

22, 2012-6

23, 2012-7

24, 2012-8

25, 2012-9

26, 2012-10

27, 2012-11

28, 2012-12

29, 2012-13

30, 2012-14

31, 2012-15

32, 2012-16

33, 2012-17

34, 2012-18

35, 2012-19

36, 2012-20

37, 2012-21

38, 2012-22

39, 2012-23

40, 2012-24

41, 2012-25

42, 2012-26

43, 2012-27

44, 2012-28

45, 2012-29

46, 2012-30

47, 2012-31

48, 2012-32

49, 2012-33

50, 2012-34

51, 2012-35

52, 2012-36

53, 2012-37

54, 2012-38

55, 2012-39

56, 2012-40

57, 2012-41

58, 2012-42

59, 2012-43

60, 2012-44

61, 2012-45

62, 2012-46

63, 2012-47

64, 2012-48

65, 2012-49

66, 2012-50

67, 2012-51

68, 2012-52

69, 2013-1

70, 2013-2

71, 2013-3

72, 2013-4

73, 2013-5

74, 2013-6

75, 2013-7

76, 2013-8

77, 2013-9

78, 2013-10

79, 2013-11

80, 2013-12

];

Sorted:

A1 As IdVal,

A2 As YrWk

resident BurnDownDates

order by A1 ASC;

// order by A1 DESC;

drop table BurnDownDates

*/

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

After I loaded the dimension file and sorted in qlikview and dropped the prior table, I still have the same issue, 32 for the aggr statement and the row is really 44. ugh.... other ideas?

Thanks,

Don

• Re: How to have the Max RowNo from an expression available for all rows?

Will put all the rows that exist in the data in the dimension table, I see there is a mismatch.  Maybe that is what was meant.

• Re: How to have the Max RowNo from an expression available for all rows?

Nope, that was not it.  Stefan, which table is wrong.  The dimension YrWk is sorted now and has values starting the same place as the data, but same result:

This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

=if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

This expression that makes that number available on all rows gives an incorrect answer of 32 now, you see what I am doing wrong:

=Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )

Where is it getting the 32 from?

• Re: How to have the Max RowNo from an expression available for all rows?

Well I think I have lost track on what is going on in your app after all...

YrWk is a key field, the load order is also determined by the order your ALL tables are loaded in (from file daily_dump_commulated.xls)

From all I can see, the load order here is not sorted according a chronological ascending way.

If I sort the list box for field YrWk by load order I get ( using your old posted app):

2011-15

2011-14

2011-16

2011-17

2011-18

2011-19

2011-20

2011-21

2011-22

2011-23

2011-24

2011-25

2011-26

2011-27

2011-28

2011-29

2011-30

2011-31

2011-32

2011-33

2011-34

2011-35

2011-36

2011-37

2011-38

2011-39

2011-40

2011-41

2011-42

2011-43

2011-44

2011-45

2011-46

2012-3

2012-4

2012-5

2011-51

2011-52

2012-1

2012-2

2011-47

2011-48

2011-49

2011-50

2012-6

2012-7

2012-8

2012-9

2012-10

2012-11

2012-12

2012-13

2012-14

2012-15

2012-16

2012-17

2012-18

2012-19

2012-20

2012-21

2012-22

2012-23

2012-24

2012-25

2012-26

2012-27

2012-28

2012-29

2012-30

2012-31

2012-32

2012-33

2012-34

2012-35

2012-36

2012-37

2012-38

2012-39

2012-40

2012-41

2012-42

2012-43

2012-44

2012-45

2012-46

2012-47

2012-48

2012-49

2012-50

2012-51

2012-52

2013-1

2013-2

2013-3

2013-4

2013-5

2013-6

2013-7

2013-8

2013-9

2013-10

2013-11

2013-12

And if I select brow, webe (first number is to indicate row number):

1  2011-18

2  2011-19

3  2011-20

4  2011-21

5  2011-22

6  2011-23

7  2011-24

8  2011-25

9  2011-26

10 2011-27

11 2011-28

12 2011-29

13 2011-30

14 2011-31

15 2011-32

16 2011-33

17 2011-34

18 2011-35

19 2011-36

20 2011-37

21 2011-38

22 2011-39

23 2011-40

24 2011-41

25 2011-42

26 2011-43

27 2011-44

28 2011-45

29 2011-46

30 2012-3

31 2012-4

32 2012-5

33 2011-51

34 2011-52

35 2012-1

36  2012-2

37 2011-47

38 2011-48

39 2011-49

40 2011-50

41 2012-6

42 2012-7

That's why you get rowno() 32 if using the aggr() function.

Not sure what you changed in the meantime, maybe you could post an updated version of your app?

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

Hmmm…  You are not talking about the dimension table but the non dimension tables such as All.  Those were created and are used by people long before me.  Not sure if I can change the order of those for I believe they use the order for some calculations, one of which may be the open calculation I am depending upon…

Let me take a look.

Thanks….

Don

• Re: How to have the Max RowNo from an expression available for all rows?

I think I have mentioned the create-the-field-values-upfront-thing.

I believe if you create all values of the field YrWk upfront, first in your load script, this will determine the load order.

Even if you then load other tables with that field any unsorted values, the initial load order will be preserved (even after finally dropping the table). Like

Autogen:

date(MakeWeekDate(2011,46+recno())) as WeekDate

autogenerate 15;

INPUT:

YrWk

2012-3

2012-4

2012-5

2011-51

2011-52

2012-1

2012-2

2011-47

2011-48

2011-49

2011-50

2012-6

2012-7

2012-8

2012-9

];

drop table Autogen;

The INPUT table is just a replacement as demo for your ALL tables etc. LOAD.

I assume that you really need to get the rowno() from within your aggr() function, or in other words, that the thing you need to do with the rowno() can't be done in a different way.

Regards,

Stefan

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

Thanks….  Am not grasping fully yet.  Got pulled to another task for today so will not be able to focus on this even though I would like to consider your approach.   Let me focus on my different new task and then come back to this.  As a result, my next question may not be today.

Don

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

Got a chance to take a look at this this afternoon and read your mail slowly.

It seemed the thing you were recommending that I did not grasp earlier was simply to move the load of my customdimension to the front of the load script.   I made that change and sure enough, it worked, now my row references work w/ aggr for that case and in general, excellent.....

Thanks for repointing out the fact that moving the dimension load to the beginning of the load script may be all that is needed, very sweet and simple.

D

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

My prior dialog indicates things worked.  There was an error in the data at that point.  The data was corrected and when I reloaded, the same issue appeared again.  I looked at the sort of the dimension in the table and indeed it is now sorted correctly but the answer is still not expected:

This expression gives me the rowno for YrWk=2012-5 as 44 which is correct.

=if(YrWk='2012-5', RowNo(),0)                                                    // gives me 44 for that row

This expression that makes that number available on all rows gives an incorrect answer of 36 now, you see what I am doing wrong:

=Max(TOTAL aggr(NODISTINCT if(YrWk='2012-5', RowNo(),0)   ,YrWk)  )

• Re: How to have the Max RowNo from an expression available for all rows?

I can't really see anything wrong. If possible, you could upload your sample again, so I can look at the raw data.

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

I believe this is working in the general case.  I have input boxes that link to expressions and regardless of the values used, works fine.

I believe the issue occurrs when Filtering is done.  This did not impact the last current row but does impact this expression.  Any way around it, the following still gives different answers if a selection filter is applied?

=Max({1} TOTAL aggr(NODISTINCT if(YrWk='2012-6', RowNo(),0)   ,YrWk)  )

• How to have the Max RowNo from an expression available for all rows?

try this

=Max({1} TOTAL aggr(NODISTINCT if(only({1} YrWk)='2012-6', RowNo(),0)   ,YrWk)  )

• Re: How to have the Max RowNo from an expression available for all rows?

Yes............. Excellent, thanks....

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

I put the expression in a variable and was trying to use on each row.  The interesting thing is that it does not allow me to use in basic math, even things such as:

=\$(StartBurnDownRowV)+2

Do you see what I am missing?  I have used in calcs before, like the LastCurrentRow calc.

Thanks,

D

• How to have the Max RowNo from an expression available for all rows?

But you get the correct number in each row when using the expression without adding some constant?

=\$(StartBurnDownRowV)

Any difference when using in a textbox?

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan

Strange.

vStartBurndownRowV=45

This correctly calculates 55i:

=10+\$(vStartBurndownRowV)

This incorrectly calculates 45:

=\$(vStartBurndownRowV)+10

=(\$(vStartBurndownRowV))+10

I must be missing something… Of course, I am trying to use the field with a calc like below for example:

(

If(RowNo()=\$(vStartBurndownRowV),  \$(vOpenErrorsTbaTbc),

If(RowNo()>\$(vStartBurndownRow), ABOVE( \$(vOpenErrorsTbaTbc), RowNo()-\$(vStartBurndownRow))                                      // make variable when startburndown row calc issue resolved

))

)

• How to have the Max RowNo from an expression available for all rows?

Sorry, no idea yet.

Maybe you should open a new thread, so you get more attention on this new, unresolved problem from the community members.

Have a nice weekend,

Stefan

• Re: How to have the Max RowNo from an expression available for all rows?

Ok, I will.  I am currently replacing the variable with the expression to see if that gets me further.

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

The long hand version gives an expected answer:

= If

(

RowNo()=  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

, \$(vOpenErrorsTbaTbc)

,If(  RowNo()>  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

, ABOVE( \$(vOpenErrorsTbaTbc), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )              )

))

Where the one using variables gives null:

(

If(  RowNo()= \$(vStartBurndownRowV),  \$(vOpenErrorsTbaTbc),

If(RowNo()>\$(vStartBurndownRow), ABOVE( \$(vOpenErrorsTbaTbc), RowNo()-\$(vStartBurndownRow))

))

)

Hmmmm.

Don

• How to have the Max RowNo from an expression available for all rows?

You could check how the dollar sign expansion is done clearing the label of the expression and then hover with your mouse over the label in your table (this should give the expression after dollar sign expansion).

Regards,

Stefan

• Re: How to have the Max RowNo from an expression available for all rows?

Stefan,

Not sure what you mean, will try a few other things.

Note, this is the fully exploded expression that does the trick however:

=

(

If

(

RowNo()=  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

, \$(vOpenErrorsTbaTbc)

,If (  RowNo()>  (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

, ABOVE( \$(vOpenErrorsTbaTbc), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )              )

)

)

)

-

(

rangesum

(

ABOVE(

If(RowNo()= (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

,\$(vOpenErrorsTbaTbc)/\$(vNumberBurnDownWeeks)

, If(RowNo()> (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

, ABOVE( \$(vOpenErrorsTbaTbc)/\$(vNumberBurnDownWeeks), RowNo()- (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    ))

)

)

, 0

, RowNo() - (    Max( TOTAL aggr(NODISTINCT if(only( YrWk)=\$(vStartBurnDownPeriod), RowNo(),0)   ,YrWk)  )    )

)

)

)

• How to have the Max RowNo from an expression available for all rows?

Not sure if this is related to your problem in any way, but you may wanna have a look into

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

which is describing the different evaluation of variables (similar to using Set / Let statements in the script).

What I meant with checking the dollar sign expansion is related to this. Sometimes it's really enlightening if you see the expression after the dollar sign expansion or variable replacement took place.

One way to see this if to look at the header in your table, if no label in expression tab is set. The header will then just show the expression, but after the dollar sign expansion took place. So you can actually see, what is replacing the variable. Maybe this gives you any insight to your problem, not sure though.