topic Re: Set analysis using date range and Date field as dimension in range selection in App Development

Set analysis using date range and Date field as dimension in range selection

shama_deshpande1 — Mon, 20 Dec 2021 21:08:46 GMT

Hi All,

I have an application where I have two date fields, i.e the Calendar Date field (which is in one table) and Date1 field (which is in another table). Further, I have associated these Date fields present in different tables with one common Key field. Now I need to calculate sum(Sales), which is present in the first table (i.e Sales field) by using Set Analysis by defining the range of these two date fields. I am using the expression :

sum({<[Calendar Date]={">=01/01/2019<=$(=[Date1])"}>} Sales)

Here, the first value in date range is fixed (i.e 01/01/2019) and the other is used as Dimension as Date1 field. I want to use Dimension i.e(Date1) in range as a dynamic field, but I am unable to do so.

I have taken a table in the front end taking Date1, Key, Sales fields, but only on the selection of Date1 field in the front end, the value of sum(Sales) comes correct. But if I select Key field, the value comes out to be incorrect.

The above expression evaluates to sum({<[Calendar Date]={">=01/01/2019<=$(=-"}>} Sales) when selection is not there or when Key field is selected.

Kindly help with this!

Thankyou,

@sunny_talwar @swuehl

Re: Set analysis using date range and Date field as dimension in range selection

tresB — Wed, 22 Apr 2020 08:44:07 GMT

When you don't select a date value or select key field it generates null in set analysis range because, one value is expected for it and there are multiple values. Though not entirely sure about your requirement, you can give a try like:

sum({<[Calendar Date]={">=01/01/2019"}>*<[Calendar Date]={"=[Calendar Date]<=[Date1]"}>} Sales)

Otherwise, try to share a sample app explaining the output and it's format in that context.

Re: Set analysis using date range and Date field as dimension in range selection

BalaBhaskar_Qlik — Wed, 22 Apr 2020 11:14:21 GMT

may be like this:

sum({<[Calendar Date]={">=01/01/2019<=$(=getfieldselection([Date1]))"}>} Sales)

Re: Set analysis using date range and Date field as dimension in range selection

shama_deshpande1 — Wed, 22 Apr 2020 11:29:22 GMT

Hi @tresB ,

I have tried the expression shared by you, but hard luck. Also, I have created a TEST SAMPLE application, where two Date fields are used. In the front end without selection, the expression evaluates like this attached (Image 1)

But on the selection of Date1 field, the expression evaluates like this attached (Image 2)

Kindly suggest on this,

Thankyou

Re: Set analysis using date range and Date field as dimension in range selection

shama_deshpande1 — Wed, 22 Apr 2020 11:30:17 GMT

Re: Set analysis using date range and Date field as dimension in range selection

shama_deshpande1 — Wed, 22 Apr 2020 11:34:44 GMT

Hi @BalaBhaskar_Qlik ,

I tried using this expression shared by you, but did not show the output.

Thankyou

Re: Set analysis using date range and Date field as dimension in range selection

afshan — Sat, 25 Jun 2022 04:19:00 GMT

https://community.qlik.com/t5/New-to-Qlik-Sense/variable-input-date-range/m-p/1948139

Re: Set analysis using date range and Date field as dimension in range selection

HeshamKhja1 — Tue, 12 Sep 2023 12:20:29 GMT

Anyone got a way to achieve this? or is it not possible in the front end?