How to filter straight table

[Anonymous]

Hi,

I am sure this should be easy to do, but I can't seem to figure out the syntax needed.

My raw data table has a date opened, and date closed columns. There will always be a date opened, but if the word order is not completed, the date closed will be blank. I have been able to use the nullcount function to count how many open work orders there are.

But I created a table and I simple want to only display records in the table that have a null "date closed" entry.

1) Can this be done without having the Date Closed dimension in my table (Since I only want to show items that have no date closed, it does not make sense to include it as a column which will never have any data)

2) If not, what is the expression I should be using? I assume this needs to go in the formula box of the (Date Closed) Dimension within my table?

I have spent about 60 minutes searching posts and working with the 600+ page PDF on scripting but nothing has worked, so any help is greatly appreciated. I am pretty tech savvy but also unfamiliar with most of the Qlik jargon which makes learning from others much harder.

Thanks

[Anonymous]

I'm not referring to a button or calculation condition.  I'm using the free version of Qlik Sense, but see if you can open this and see how I've done it.

[Anonymous]

Wallo,

Thanks for taking the time to try to help me. I was able to download your file and open it myself. The table you created is exactly what I need ( I don't need the "days open" calculation, but I don't mind having it there).

I simply do not understand how to create this table. Where is the formula that excludes the closed records?

I have already added a new column in my excel file for the raw data for Status. So I don't need a load script. I just want to filter my chart table for Status=Open.

What is the syntax?

I have tried all combos I could think of, but I do not understand how the < [ { and ( are supposed to be used and it seems I just can't get the syntax correct.

I am trying to attach my project as an example file but not sure how. Does this link work for someelse? https://community.qlik.com/docs/DOC-18573

[Anonymous]

That link didn't work for me.

The text within the {<  >} is called set analysis.  It's a way to modify your base data. 

When you create a table and you click 'Add Column' you have the option of creating a Dimension or Measure. 

Generally speaking a dimension would be just a field that you just want to show.  A Measure is a column that performs some kind of calculation.  By adding the Measure with the set analysis it filters the data down to just those dimensions that meet the set analysis expression.

To see the Measure I added, go to Edit, then Data, then click on the column 'Days Open'.

[Anonymous]

Thanks Wallo.

I definitely want everything to show up as dimensions, I want this page to look identical to the excel file I upload. The only difference is that I only want to see what is open.

I want to learn how to do this without having to create a new column called "Days Open". Why can't I use the "status" column on it's own to filter so I only see records that have a status of 'Open'? Do I have to use Status as a measure instead of a dimension to accomplish this? Everytime I use status as a meausre, it is returning a -1 or 0 value which is not what I want.

[Anonymous]

I am trying to create the set analysis but it seems the syntax I use is always wrong.

[Anonymous · Accepted solution]

You would use set analysis in an Measure/expression.

If you don't want any extra columns you can use what Oleg mentioned.  You would still need a Measure.

Then it could be

min( {<Status={'Open'}>} [Date Opened])

That's the exact syntax you would need

[Anonymous]

Adding a new meausre to my table, and using the expression you provided: min( {<Status={'Open'}>} [Date Opened]

This is still generating a new column in the displayed data, so I'm not sure how this is different than using the Days Open calculation.

Since I already added the "status" column to my data, I guess I will just add a filter pane for "Open/Closed" at the top and the user can click Open and then view the data within the table correctly. I wish there was a better way to restrict the table view without having to first require user input, but it seems it is too complicated for me to execute.

Thanks so much for your time and knowledge. I don't know what motivates people like you to help novice users like myself, but it is absolutely greatly appreciated even if I was not able to resolve my issue.

[Anonymous]

You're almost there! 

With what you have you would remove the Dimension column of 'Date Opened'.

And then in your Measure column with your formula, you would change the label box to be 'Date Opened'.

Yes, you could also just leave it as a filter option too.

Good luck to you!

Please mark a correct answer to close this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

[Anonymous]

Thanks again Wallo.

I will play around with this and hopefully get it working. I tried to find a tutorial on QlikSense syntax, but all my search results seem to be related to QlikView so it was difficult to find the right resources.

[Anonymous]

You're welcome.

Actually the Help in Qlik Sense itself is pretty good.  That would probably be a good starting point.