Table filter possibility

[JeroenHoltrop]

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?  

[JHuis · Accepted solution]

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])

[JeroenHoltrop]

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

[JeroenHoltrop]

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

[JeroenHoltrop]

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

[JHuis]

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;) 

[JeroenHoltrop]

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

[JHuis]

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!

[JeroenHoltrop]

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. 

[JHuis]

Jeroen,

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