Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ioannaiogr
Creator II
Creator II

need a date field to get dynamically values from canonicaldate field (set analysis)

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👩‍💻👨‍💻

 

 

Labels (2)
1 Reply
AustinSpivey
Partner - Creator
Partner - Creator

  1. Your set analysis is fine, it looks like you just have a casing issue here: 
    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."

  2. Not sure I understand what you're saying here. You want to be able to select the [Year_cancelation_date] field and have that dynamically select the same values in the [Year] field? If that's the case, it seems like that would just need to be done in the data model, where you'd associate those fields together, perhaps using the [Date Bridge] table you mentioned. You may be able to achieve what you want just in the frontend by using set analysis, but you will probably get wrong/slow calculations due to expensive look-ups and possible circular dependency-like behaviors.

    To know for sure, we'd need to have a look at the data model and get some clarity on what it is you specifically want to achieve.
Austin Spivey | Principal Consultant @ Arc Analytics
ArcAnalytics.us | Add me on LinkedIn