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

[shama_deshpande1]

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 

[tresesco]

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.

[balabhaskarqlik]

may be like this:

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

[shama_deshpande1]

Hi @tresesco ,

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

[shama_deshpande1]

Hi @balabhaskarqlik ,

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

Thankyou 

[afshan]

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

[HeshamKhja1]

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