Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
v_jaideep
Creator
Creator

If statement in Set analysis

Hi,

I need sum(sales) only when start date > approved date. How can I achieve this in set analysis .

@tresesco any suggestion

14 Replies
tresesco
MVP
MVP

Hi @v_jaideep,

This field comparison set analysis is little different from other usual set analysis. The implementation might differ based on your data model - if the two fields are from same table or not, if they are in one-to-one relationship or not....few such questions need to be answered. Hence it is better if you could share a small presentative data set/app to work on and help you.  

v_jaideep
Creator
Creator
Author

Hi @tresesco Both are in different tables, have one-one relationship. Table structure will be as below. Date Format for both of them are in MM/DD/YYYY.

I don't want a join as size of data is causing performance.

TABLE A  TABLE B
start date  approved date
product id  product id
price  category
product name  sub category
AnalyticsBoost
Contributor III
Contributor III

You need to convert the date format to numerical in order to make comparisons something like Num(Date#([StartDate], 'MM/DD/YYYY')))>Num(Date#([ApprovedDate], 'MM/DD/YYYY')))

simospa
Partner
Partner

Hi,

can you consider to add a flag in the loading script, something like:

if(startDate >= approveddate, 1, 0) as Flag

and then this in your SetAanalysis

=Sum({$<Flag = {1}>} Sales)

S.

v_jaideep
Creator
Creator
Author

@simospa I don't want to create a flag .

To create a flag  I need to join as both of them are from different tables and with datasize I have it is taking time to reload.