Calendar filter pane and multiple conditions

[AW_QLIK]

Hi,

I have a filter pane which has month end dates:

When choosing one month end date from this pane then only those "DocNum" shall be selected where "DocDate" is <= "Calendar.Date" AND "ClearDate" is > "Calendar.Date". Here is an example:

DocNum	DocDate (DDMMYYYY)	ClearDate (DDMMYYYY)
1	15.06.2022	15.10.2022
2	02.03.2022	01.04.2022
3	20.09.2021	01.05.2022

So by selecting 30.04.2022 the result should be:

DocNum	DocDate	ClearDate
3	20.09.2021	01.05.2022

Right now I have no idea which function to use to get this solved. Hope you can help me out as I am new to QLIK.

Best regards.

[vinieme12]

try below

count({<DocDate={"<=$(=Date(Max(Calendar.Date))"},ClearDate={">$(=Date(Max(Calendar.Date))"}>}DocNum)

[AW_QLIK]

Hi Vinieme12,

I understand the idea of this, but I don't know how and where to implement it.

Can you please provide a few more details?

I have the filter pane with the dates and a separate table with the three columns. The date table and the other table are not connected.

Thank you.

Best Reagrds.

[vinieme12]

In a Table chart

Add Dimensions as below

=Aggr(count({<DocDate={"<=$(=Date(Max(Calendar.Date))"},ClearDate={">$(=Date(Max(Calendar.Date))"}>}DocNum),DocNum)

=DocDate

=ClearDate

[AW_QLIK]

Thank you Vinieme12.

Actually this is still not working. When I select a date from Calendar.Date then nothing happens in my table BSAD (here I am using the original system data).

My Calendar is not linked to the table BSAD. Also when I link it to BSAD, which I don't want to, this is not working.

May be I have to work with a variable??? But I don't know how I can get the selected date from Calendar.Date into a variable.

[vinieme12]

You don't need to wrap Count() in aggr()  ,  count() itself is an aggregation function

refer this article

https://community.qlik.com/t5/Qlik-Design-Blog/It-s-all-Aggregations/ba-p/1465669

And please do ensure all your tables,including calendar is associated to your data 

refer this article for more links on link tables

https://community.qlik.com/t5/QlikView-App-Dev/what-is-link-table-how-it-works/m-p/1041832

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578