If statement in Set analysis

[v_jaideep]

Hi,

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

@tresesco any suggestion

[tresesco]

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]

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]

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]

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]

@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.