Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shama_deshpande1
Contributor II
Contributor II

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

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 

7 Replies
tresesco
MVP
MVP

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

 
shama_deshpande1
Contributor II
Contributor II
Author

Hi @balabhaskarqlik ,

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

Thankyou 

HeshamKhja1
Partner - Creator II
Partner - Creator II

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