Maxstring() - 1?

[richnorris]

Hi,

I have a list of duals ([Reporting Period]) created from an inline load, and I want to be able to pick the current period (maxstring([Reporting Period]) which is fine, and also the prior period Maxstring([Reporting Period]) -1 effectively. Unfortunately however I slice it, if I do any kind of numeric function, it returns the number of the dual. Unfortunately it seems that once it converts it to a num() it loses the associated text half of the dual for some reason.

Is there any way to use a 'Dual' like an array, and pick the th value from it? It seems quite limited what you can actually do with a dual.

[swuehl]

Try maybe

= Only( {<[Reporting Period] = {"=[Reporting Period]=(Max([Reporting Period])-1"}>} [Reporting Period])

or just apply a formatting function to the original returned number to create the dual.

Another option would be to use FieldValue( index), but this operates on the field index in the symbol table (load order).

[richnorris]

The FieldValue() would so nearly solve the problem (since I load it from an inline statement I can control the load order, so thats fine) but unfortunately it will run into issues when the month is January, since the previous period will actually load later (December) and the whole thing falls apart. It might be that the best way is actually to do this anyway, but have a hard coded version for if the month is Jan, though it gets kinda fiddly with the year portion.

[swuehl]

If your [Reporting Period] is cyclic, I think a linked field that is sequential would better serve your needs:

Cyclic or Sequential?

For example, create a PeriodNum Field in the model like

Year*12+ MonthNum as PeriodNum,

Then you can easily use this PeriodNum field to traverse back and forth in time by just adding / subtracting 1.

[richnorris]

Yeah, I mean, thats what I've done. But if you do (January-2015) - 1 you get a number, not a text representation of December-2014 .It doesn't seem to matter what you do, if you manipulate the number of a dual in any way, Qlikview seems to forget that it was a dual and treats it as purely a number.

[swuehl]

That's true, a numeric operation will return a number.

[Anonymous]

I'm experiencing exactly the same problem... anyone else tried to sort this out?

[richnorris]

In the end what I ended up doing was a horrible mishmash of hacks. Basically, I manually created a second field called [Prior Reporting Period] that was another dual, but with the number of the previous month (so Jan 12, Feb 1, Mar 2 etc). It might make more sense for you to do it the other way round, (join on the number, have the number associated with the previous month) but I went through a bunch of mappings and that ended up being the way that worked for me.

[mlgross]

This is an older post but I thought it might be helpful to explain how I got around this issue. I had a "season" field that I needed to use to make a calculation for the previous 5-season average. I tried to use "maxstring" but had issues with it since I couldn't do maxstring() -1. Instead, I realized that the user would need to be able to select the season they wanted to view anyway (and that the 5-season average calculation would be based off of this) so I put the selection as a variable input and then used the variable selection within a combination of fieldindex and fieldvalue to get the previous season like this:

=fieldvalue('i.Season',fieldindex('i.Season','$(vSeasonSelect)')-1)

Hope this helps!