Straight-table - building several filters into the dimension

[datanibbler]

2"
Hi,

I am just working on a new straight-table chart and for some reason I can't get it to work properly. Maybe someone here can help me:

- The chart involves several tables taken from our database.

- The dimension is basically supposed to be the Item_Nr - but:

   - When the current time is earlier than 2pm (early shift), there should be two filters for

       - the date (available in 'DD.MM.YYYY' format) being "yesterday"

       - the time (available in 'hh:mm:ss' format) being "after 2pm" (late shift)

   - When the current time is later than 2pm (late shift), there should be two filters accordingly for

         - the date being "today"

         - the time being "before 2pm" (early shift)

- Moreover, there has to be a filter for another field (the reception channel) where I have to filter (independent of the current time) for

   one specific value.

I tried building two separate dimensions, each one with a condition rgd. the current time, but for some reason that did not work.

I will post here my formulas. I guess there must be some error in one of them.

>>> Dimension 1 (for the early shift) <<<

= IF((DATE(FLOOR(INVT_TRANS_DETAIL.TRAN_DATE)) = DATE((Today()-1))

      AND TIME(FRAC(INVT_TRANS_DETAIL.TRAN_DATE), 'hh:mm:ss') >= '14:00:00'

      AND (INVT_TRANS_DETAIL.TRAN_CODE = 'SPRECEIVE')),

           INVT_TRANS_DETAIL.ITEM_NUMBER)

Condition for this dimension to be active: TIME(Now(0), 'hh:mm:ss')<'14:00:00'

>>> Dimension 2 (for the late shift) <<<

= IF((DATE(Floor(INVT_TRANS_DETAIL.TRAN_DATE)) = DATE(Today())

        AND TIME(Frac(INVT_TRANS_DETAIL.TRAN_DATE), 'hh:mm:ss') < '14:00:00'

        AND (INVT_TRANS_DETAIL.TRAN_CODE = 'SPRECEIVE')),

                  INVT_TRANS_DETAIL.ITEM_NUMBER)

Condition for this dimension to be active: Time(Now(0), 'hh:mm:ss') >= '14:00:00'

For some reason, this seems to work fine now, but it does not show the correct data:

=> As one formula, I have just used the date (in the first line of my dimension, I even copied exactly that formula) - and now (9:43am), the 1st dimension should  be active, so that date should be yesterday's date - but it is today's date ...

Can anybody help me with this, please?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

I think I can break it down - I just noticed there seems to be a fundamental problem which might be at the core of this:

- The expression TIME(NOW(0), 'hh:mm:ss') returns 09:43:12 - or so, the same thing ReloadTime() would return.

<=> When I write >> IF(Time(Now(0), 'hh:mm:ss') < '14:00:00', 'early', 'late') << - that returns 'late'

How come?

P.S.: Now I tried making it in a different way - a bit more complicated: I built - inside-out - the formula

>>> TIME(FRAC(NUM(Now(0)))) <<<

That formula returns just the same, the current time - but when I build that into an IF() formula, it returns "early" like it should.

Can anybody explain why it has to be so complicated?

[antoniotiman]

Hi,

Try

If(Text(Time(Now(0),'hh:mm:ss')) > Text(Time('14:00:00','hh:mm:ss')),'early','late')

Regards

[tresesco · Accepted solution]

Time() is just formatting an underlaying numeric value without changing it's base value. Now() gives you something a number like: 71795.12344 or so. When you write: IF(Time(Now(0), 'hh:mm:ss') < '14:00:00', 'early', 'late'), IF actually tries to compare the underlaying numeric value with your '14:00:00'  which is a string. Then the result is always FALSE. Hope this helps.

[datanibbler]

Hi tresesco,

yes of course, this clarifies the matter. But - if Now(0) gives me a number (numeric?) and TIME() formats this, is the result numeric or is it a string?

=> Since it didn't work that way, I assume the result is still numeric and cannot be compared with my '14:00:00'?

=> So what is the difference between that and my new formula? When NOW(0) returns a number, then the use of FRAC(NUM()) should not make a difference, should it?

[tresesco]

Frac() is not a formatting function, it really discards the integer part from a number and returns the fraction. If you have a number  71795.12344 for now(), its integer part is for days (1=1 day). fractional part comes under time. 1/24 = 1 hour. That means the fractional part is actually responsible for time. Hence you should ideally compare like:

If( frac(Now()) >14/24, ....)     // 14/24 would give 14 hours.

But yes, your one point is correct, '14:00:00' would not really been treated as string but, qv is intelligent enough to understand the format (possibly from script format declartion) and treat 14 as hour. So, '14:00:00' would actually been converted to equivalent number 14/24 and compared.