topic Re: How to filter the data table using Filter pane value in Qlik Sense desktop? in Reporting Service & Alerting

How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Tue, 26 Nov 2024 09:54:09 GMT

I have a data table, so I need to filter the data using a 'greater than or equal to' condition based on the values selected by the user from the filter pane.

Tried the above expression
Sum({<[Date_of_Last_Invoice]= {"<=$(=Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD'))"}>}Invoice)

But shows like Error in expression.

@devarasu07

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Ruggero_Piccoli — Tue, 26 Nov 2024 10:39:46 GMT

Hi,

Could you share the error message?

Qlik Sense stores dates as numeric fields, so check that [Date_of_Last_Invoice] is stored as date field. Be sure that all data types involved in the expression are consistent.

You can use temporary text boxes to verify the results of Min([Date_of_Last_Invoice] and of Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD').

Best Regards,

Ruggero

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Tue, 26 Nov 2024 12:04:40 GMT

Hi,

Thanks for your Support.

Verified the results, and it showing as expected, like Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD').

But when I try to use this line
"Sum({<[Date_of_Last_Invoice]= {"<=$(=Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD'))"}>}Invoice)"

showing error.

Please find the attachment.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Ruggero_Piccoli — Tue, 26 Nov 2024 13:25:20 GMT

Hi,

Try Sum({<[Date_of_Last_Invoice]={">=$(=Min([Date_of_Last_Invoice]))"}>} Invoice).

This Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD') is returning a string but the comparsion is with the numeric value [Date_of_Last_Invoice] I suppose.

Best Regards,

Ruggero

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Tue, 26 Nov 2024 13:34:08 GMT

I tried the Sum({<[Date_of_Last_Invoice]={">=$(=Min([Date_of_Last_Invoice]))"}>} Invoice), but it is still showing an error in expression.

Please find the attachment.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Ciya — Wed, 27 Nov 2024 06:51:31 GMT

Hi Tamizh,

Try below expression. use ' instead of ". It's worked for me.

Sum({<[Date_of_Last_Invoice]= {'<=$(=Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD'))'}>}Invoice)

Thanks,

Chaithra

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Wed, 27 Nov 2024 07:15:49 GMT

Hi Chaithra,

Thanks for your support.

I have tried but it still showing an error in expression and Column invalid dimension. Apart from anything else, I need to modify?

Please find the attachment.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Eva_L — Wed, 27 Nov 2024 07:49:18 GMT

In the attached image it looks like it does not know your measure field "Invoice". Is it maybe in lower case or did you rename it?

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Wed, 27 Nov 2024 08:06:44 GMT

Thanks for your support.

I have tried using different options, as shown in the attachment (Invalid Dimension). Since I am using 'Invoice' as the alias name for the expression, how should I choose the name?

Could you help me on this?

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Ciya — Wed, 27 Nov 2024 08:25:06 GMT

I have tried below expression in my report,

Sum({<[Order Date]= {'<=$(=(Min([Order Date])))'}>}COGS)

where,

Measure = COGS

Dimension = [Order Date]

There are fields should highlight in red. I think in your screenshot Invoice is in Black color. Please check once.

Replace your metrics.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Eva_L — Wed, 27 Nov 2024 08:35:29 GMT

If you want to combine the two you would only get values for the min Invoice date as one is set to to <= min date and the other to >= min date.

You could still use your master measure if your qlik sense version is high enough

{<[Date_of_Last_Invoice]= {"<=$(=Date(Min([Date_of_Last_Invoice]),'YYYY-MM-DD'))"}>} [Invoice]

where [Invoice] should link to the master measure.

However; you could just use your variable value again from your original formula if you don't want the intersection of the two (aka just the min invoice date)

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Wed, 27 Nov 2024 08:46:04 GMT

Thanks for your support.

Could you please clarify how to create a measure? I am using a data table with columns containing dimension values from the SELECT query, but I am unsure how to use the measure.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Ciya — Wed, 27 Nov 2024 09:15:28 GMT

Which data you want to filter in this table using [Date_of_Last_Invoice] field?

Based on the expression you provided "Invoice" field should filter but this field is not present in your table. To filter the "Invoice" data using the [Date_of_Last_Invoice] field, you either need to have an "Invoice" column in the table or derive it using your business logic.

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Wed, 27 Nov 2024 11:26:30 GMT

Thanks.

Yes, Invoice does not present in our table using Date_of_Last_Invoice . I need to filter the Date_of_Last_Invoice using a 'greater than or equal to' condition based on the values selected by the user from the filter pane. Also, the filter pane contains Date_of_Last_Invoice values.

Now expression gets resolved but invalid dimension.

This is my query logic

Re: How to filter the data table using Filter pane value in Qlik Sense desktop?

Tamizharasan1 — Wed, 27 Nov 2024 17:34:06 GMT

Thanks for your support.
Don't have the "Invoice" field but I need to filter the "Date_of_last_Invoice" from the filter pane and filter the "Date_of_last_invoice" in datatable with condition greater than or equal to.