cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional formula based on value from chart

TomerA
8 - Cloud Apps
8 - Cloud Apps

Hi,
I have created a chart that is base on launch day and I want to count the number of IDs per each date in the chart but only if the ID has last_seen date that is >= from the day on the chart.

TomerA_0-1641904581863.png

And this the chart with only count unique IDs without the condition.

TomerA_1-1641904693149.png


Is this possible?

3 REPLIES 3

wallingfordce
8 - Cloud Apps
8 - Cloud Apps

Something like this maybe? For each ID on that launch_date, if last_seen is before or same as launch_date then include the ID in a unique count.

COUNT ( [ID] , 
IF (
MIN ( DDIFF ( [Days in last_seen] , [Days in launch_date] ) ) <= 0
, [Max ID]
, NULL
)

My issue is actually different then I mistakenly described. Created a new post.
https://community.sisense.com/t5/build-analytics/how-to-create-a-table-with-a-range-of-dates/m-p/185...

hlorosc
9 - Travel Pro
9 - Travel Pro

Another way (if I'm understanding correctly) would be to add a "helper column" at the data model level

Custom column would be:

CASE WHEN DATE_TRUNC(last_seen, DAY) >= DATE_TRUNC(launch_time) THEN 1

ELSE 0 

END

 

Then you can filter your unique count by that field (and only include those with a value of 1)