Sliced Data not shown in view

Michael2
Participant V

Data that has been sliced (filtered) based on one field is returned correctly in the slice but the view based on this slice is empty. Anyone have any ideas what the issue could be?

The field being used to slice the data is a VC lookup to another table.

Solved Solved
0 26 1,481
1 ACCEPTED SOLUTION

26 REPLIES 26

Steve
Participant V

More details?

Two tables, Circuits & Circuit_Notes. The VC looks up the related rows in the Circuit_Notes table, select the row with the most recent date (MAXROW) and returns the value of field IsActive. If there are no related rows in the Circuit_Notes table, the expression returns TRUE.

IF(
ISBLANK(SELECT(CIRCUIT_NOTES[IsActive],[NOTEID]=
MAXROW(โ€œCircuit_Notesโ€,โ€œDATEโ€,[CIRCUIT]=[_THISROW].[CIRCUIT]))),โ€œTrueโ€,

SELECT(CIRCUIT_NOTES[IsActive],[NOTEID]=
MAXROW(โ€œCircuit_Notesโ€,โ€œDATEโ€,[CIRCUIT]=[_THISROW].[CIRCUIT]))
)

Data sliced on [IsActive]=TRUE returns the expected data, only the Active circuits. Views based on this slice are empty.

SELECT() returns a list of values, but you only want a singular value from the second SELECT(). Try wrapping it with ANY():

IF(
  ISBLANK(
    SELECT(
      CIRCUIT_NOTES[IsActive],
      (
        [NOTEID]
        = MAXROW(
          "Circuit_Notes",
          "DATE",
          ([CIRCUIT] = [_THISROW].[CIRCUIT])
        )
      )
    )
  ),
  "True",
  ANY(
    SELECT(
      CIRCUIT_NOTES[IsActive],
      (
        [NOTEID]
        = MAXROW(
          "Circuit_Notes",
          "DATE",
          ([CIRCUIT] = [_THISROW].[CIRCUIT])
        )
      )
    )
  )
)

No dice, still blank.

What are you using to format your expression? Surely youโ€™re not doing that manually?

Just using a simple text editor. The website is doing the colorizing.

Steve
Participant V

Also, that expression of yours is hugely expensive!

For each row of CIRCUITS: 1) look at every row of CIRCUIT_NOTES; 2) for each row of CIRCUIT_NOTES, look again at each row of CIRCUIT_NOTES; 3) if the circuit occurs in CIRCUIT_NOTES, repeat (1) and (2).

If N is the number of rows in CIRCUITS, M is the number of rows in CIRCUIT_NOTES, and every circuit is represented in CIRCUIT_NOTES, youโ€™re doing 2M(N^2) row inspections!

Eek!

I suggest creating a virtual column (e.g., LATEST_NOTE) to compute MAXROW(...):

MAXROW(
  "Circuit_Notes",
  "DATE",
  ([CIRCUIT] = [_THISROW].[CIRCUIT])
)

then rewrite the existing VC app formula to make use of it:

IF(
  ISBLANK(
    SELECT(
      CIRCUIT_NOTES[IsActive],
      ([NOTEID] = [LATEST_NOTE])
    )
  ),
  "True",
  ANY(
    SELECT(
      CIRCUIT_NOTES[IsActive],
      ([NOTEID] = [LATEST_NOTE])
    )
  )
)

which brings you down to 3MN. Still more than necessary, but a lot better! But you can reduce further:

IF(
  ISBLANK([LATEST_NOTE].[IsActive]),
  "True",
  [LATEST_NOTE].[IsActive]
)

which brings you down to MN row inspections.

Thank you for pointing out how expensive my expression was and rewriting it for me. Unfortunately, the results are the same: Expected data is returned in the slice but not in the view based on the slice.

I built a test app if youโ€™re interested and available to take a look?

As for the expression, I was curious as to how the expression was being written in regards to the segmentation rather than the color formatting. Where each part of the expression is broken into individual segments.

Iโ€™d be happy to. sc2758g@gmail.com

Invited. Thank you Steve

Can I get edit access? I canโ€™t see the config, only the running app.

Thats odd, I invited you and set your role as Admin:
2X_3_3a40ae82d15bbb20f57f0b50ba22f67759c76a4a.png

Iโ€™m in.

I had the editor open until just a second ago. Think that made a difference?

No. I was looking for an Edit option in the drop down; didnโ€™t try just clicking in the image. Used to be an Edit optionโ€ฆ grumble

Iโ€™m guessing mean the blocks enclosing the code fragments? Precede and follow the snippet with a line consisting of three consecutive backticks (```).

Interestingโ€ฆ Thank you for the tip!

Steve
Participant V

So letโ€™s look at this further.

How are you determining the data is returned correctly in the slice?

What type of view is it?

Do any of the columns have Show? set to OFF, or have a Show_If expression that might hide them?

Each slice has a 'View Data" button. Utilizing this feature, I am able to see the data the slice returns.

A table view yields no results.

No columns are hidden or using a Show_If expression.

IsActive is a Text column. Is there a reason it isnโ€™t Yes/No?

No reason other than thatโ€™s what it defaulted to.

Shouldnโ€™t make a difference, but Iโ€™d recommend changing it to Yes/No.

I noticed that it was text rather than yes/no but same as you, I didnโ€™t think it should make a difference and havenโ€™t tried changing it. Since youโ€™re there, change it and see if that works.

Bingo!

Donโ€™t you love & hate it when it so simpleโ€ฆ Rather annoying reallyโ€ฆ

Thanks for your help Steve.

Incredibly annoying! And I have no idea why it made a difference.

Happy to help. Remember to remove my access.

Youโ€™re awesome man, really appreciate it.

Top Labels in this Space