topic Problem with ColumnNo () in QlikView

Problem with ColumnNo ()

bettina13 — Thu, 06 Oct 2016 11:32:07 GMT

Hello,

I have ab Problem with ColumnNo () used in a Pivottable.

In the example attached the led-light should be yellow when the Columns Show the same value. But one is red, one is yellow.

Here the formula:

If (ColumnNo () = 2 and Sum ( If (Stand_Tag = Today () -1 , Ticket_Anzahl_offen_Tag)) > 0,
If ( Sum ( If (Stand_Tag = Today () -1 , Ticket_Anzahl_offen_Tag)) > Sum ( Ticket_Anzahl_offen_Tag_Vormonate) / 1 * 110 / 100,
'qmem://<bundled>/BuiltIn/led_r.png',

If ( Sum ( If (Stand_Tag = Today () -1 , Ticket_Anzahl_offen_Tag)) < Sum (Ticket_Anzahl_offen_Tag_Vormonate) / 1 * 90 / 100, 'qmem://<bundled>/BuiltIn/led_g.png' ,
'qmem://<bundled>/BuiltIn/led_y.png')),
If (ColumnNo () = 2, 'qmem://<bundled>/BuiltIn/led_g.png' ))

The 3rd Dimension "Stand_Tag" is the title of the Columns.

It seems to be a bug in QlikView.

We are using QlikView for Windows Version 11.20.13405.0 SR 15 64-bit-Edition.

Has anyone an idea for that?

Re: Problem with ColumnNo ()

sunny_talwar — Thu, 06 Oct 2016 17:22:09 GMT

I think you might need to use Before/After functions to do this, but difficult to recommend anything before checking it out. Would it be possible to share a sample?

Re: Problem with ColumnNo ()

bettina13 — Mon, 17 Oct 2016 08:04:35 GMT

Hello Sunny,

thanks for your answer, but the Problem is, that with a previous version it worked. With the actual patch it doesn't work any more.

I have a similar problem in charts, where I use Before/After. But there I can use the columnName instead of ColumnNo.

Re: Problem with ColumnNo ()

sunny_talwar — Mon, 17 Oct 2016 16:14:41 GMT

Difficult to say much without taking a stab at the issue. Would you be able to provide a sample to look at? Also provide expected output based on the sample provided.

Re: Problem with ColumnNo ()

bettina13 — Tue, 18 Oct 2016 13:22:02 GMT

Hello Sunny,

please see the example attached. Sorry, it is in German.

Please compare the first line of "Hamburg", named "Bearbeitung erledigt"

with the first line of "Oldenburg", named "1st Level support". The numbers

in the second column is the same as in the first column, so the led should

be yellow. For Oldenburg it is red.

Hope, you have an idea.

Mit freundlichen Grüßen

Dr. Bettina Schewe

- Geschäftsentwicklung -

Re: Problem with ColumnNo ()

sunny_talwar — Tue, 18 Oct 2016 19:31:52 GMT

Hi Bettina -

I don't think you are doing a straight comparison between the two columns

If ( Sum ( If (Stand_Tag = Today () -1 , Ticket_Anzahl_offen_Tag)) > Sum ( Ticket_Anzahl_offen_Tag_Vormonate) / 1 * 110 / 100,

'qmem://<bundled>/BuiltIn/led_r.png',

You to seem to be checking greater then and less than comparison. and that to not with the striaght value of this period, but with 1 * 110/100. So the comparison is taking place like this:

Here you can see that Hamburg is comparing 3 to 3.3 and 1st Level Support is comparing 6 to 5.5.

Is there a reason you are multiplying by 110/100 and 90/100?? May be those are not needed and you need to check for equality?

Re: Problem with ColumnNo ()

bettina13 — Wed, 19 Oct 2016 13:09:48 GMT

Hello Sunny,

thanks for your tip.

The comparison was o.k., but I the value of one field was wrong.

I found a problem there and now it works.

Bettina

Re: Problem with ColumnNo ()

sunny_talwar — Wed, 19 Oct 2016 19:06:44 GMT

Great, I am glad you found the issue. Please close this thread by marking the correct response and any helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny