Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone.
I have lots of dates in my tables, so I created a canonical date called CanonicalDate. In my date master calendar, the CanonicalDate now has a field called WholeYear which is like 2020-2021, 2021-2022 etc.
I have to calculate a measure like: count the ids that ever had a delivery_date, a canceled_date in the selected WholeYear, a status as 'cancelled'.
I tried looking at it with 2 ways but I am not really sure:
1)
count({<Status={'Cancelled'},delivery_date={"=(len(delivery_date)>0)"},cancelation_date={"=(len(cancelation_Date)>0)"},DateType={'Cancelled'}>} distinct ID)
using flag DateType 'Cancelled' from date bridge table.
2) Would like to know how I could do it with field selections dynamically
Created Year_cancelation_date so that I can translate WholeYear in just Year eg. 2020-2021 as 2020,2021 and then in set analysis say Year={"=GetFieldSelections(Year_cancelation_date)"} but for when field WholeYear is selected.
In my mind I want WholeYear to be selected and my set analysis to do the calculations for split years.
I am missing the execution part.
Please, I would like a helping hand👩💻👨💻
cancelation_date={"=(len(cancelation_Date)>0)"}
That cancelation_Date should match the casing of your field, so it should be all lowercase, like cancelation_date. Also, if you're just trying to find non-null values in the cancelation_date and delivery_date fields, you can use an asterisk inside your double quotes, like: cancelation_date={"*"}
That's basically the same thing as saying "include all non-null [cancelation_date] values in this set."