topic Re: Table filter possibility in App Development

Table filter possibility

JeroenHoltrop — Fri, 17 Dec 2021 09:03:22 GMT

Hi,

I have an issue which I do not know how to solve. Normally I make filters based on dimensions, where I filter out the null values and my table is exactly as I want. However, with my current table only has measures, so this method is not possible. I however need a way to only show the last 2 lines in my below screenshot, so only the latest dates in the below screenshot. Does anyone know how to do this?

Re: Table filter possibility

JHuis — Fri, 17 Dec 2021 13:56:40 GMT

Dear Jeroen,

I hope I understand your question you can use set analysis for this:

For example:
Sum([Total Paid Commission])

Transfer it to:

Sum({<Comission approval date={"$(=Maxstring(Comission approval date))"}>}[Total Paid Commission])

Re: Table filter possibility

JeroenHoltrop — Fri, 17 Dec 2021 14:16:22 GMT

Aha, indeed, this is a different approach that I did before, this means that on all columns this condition should be built in. I will try to do this and let you know the outcome.

Thanks for your suggestion, this is really appreciated

Re: Table filter possibility

JeroenHoltrop — Fri, 17 Dec 2021 16:05:44 GMT

Perfect, this is the solution for my issue, thank you very much. Enjoy your weekend!

Re: Table filter possibility

JeroenHoltrop — Mon, 20 Dec 2021 05:37:01 GMT

Hi,

I have an additional challenge, which I thought I should be ablt to handle myself, but it seems that Qlik and I do not speak the same language here :). I have multiple "names" (first column in the screenshot), which all have different maxstrings, but I need to show this data for all the names, and the maxstrings then per name.

Any idea how that should be achieved?

Thanks a lot

Re: Table filter possibility

JHuis — Mon, 20 Dec 2021 07:34:18 GMT

i have an idea;

you have to put the column name into the formula with an =. like this:

Sum({<COLUMNAME=,Comission approval date={"$(=Maxstring(Comission approval date))"}>}[Total Paid Commission])

Otherwise; send me an excel file of your data with your columnames and dummy data, i will do the work for you;)

Re: Table filter possibility

JeroenHoltrop — Mon, 20 Dec 2021 11:10:16 GMT

I tried it on all possible ways I could think of, but I kept on getting only the lines with the highest approval date. I made an export of the data and left only 5 names with different approval dates in it.

I hope with this you are able to find Columbus'egg for me 🙂

Thank you very much

Re: Table filter possibility

JHuis — Wed, 22 Dec 2021 10:50:21 GMT

Jeroen,

sorry for my late reaction:

if you put in this for the different measures:

=sum([Total Paid Commission]*if([Commission approval date]=aggr(nodistinct maxstring([Commission approval date]), [Broker name],Energiesoort),1,0))

And supress zero values in the tabel it will be everything you need.

Let me know!

Re: Table filter possibility

JeroenHoltrop — Wed, 22 Dec 2021 16:59:34 GMT

Dont worry about your late response, I am very happy that you take the time to try to help me!

I didn't try this yet (maintenance issue with our Qlik environment), but already a question, how will I be able to suppress the zero values in a table, is that a table setting or something? I searched for this before but could not find this setting with measures in the table.

Re: Table filter possibility

JHuis — Thu, 23 Dec 2021 07:23:35 GMT

Jeroen,

my qliksense is in dutch, but you have to supress zero values in the edit mode from the table.

Re: Table filter possibility

JeroenHoltrop — Fri, 24 Dec 2021 14:47:31 GMT

Hi,

Mine is in dutch as well, so I found the setting. That indeed seems to do what I hoped to find earlier, so a little sidecatch 🙂 thanks for that.

I am still puzzling with my whole table, as there are a lot of calcs done to get the numbers that I have in the table. Adding more aggregations, makes it hard for me to make sure I have the right numbers, but I have faith that this will be the solution.

Thanks very much so far and fijne kerstdagen!

Re: Table filter possibility

JeroenHoltrop — Tue, 28 Dec 2021 16:33:28 GMT

Hi,

Just a little heads up that with your guidance I made the report working as I intended. I now have a report with only the latest data in the overview per entity, which is very helpfull for my business.

Thanks again for your help, couldn't have done it without you 🙂

Happy new year!

Cheers, Jeroen