Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community.
I am trying to write an expression that uses concat and dual, what I am trying to do is combine data (attached sample table) and get the result on the desired table tab.
Any help rendered will be truly appreciated.
It's because you're using the Only() function which returns NULL if you have more than one value.
You need to include the Stage and the Dates in the same concat:
If( Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage,',')<0,'',
Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage & chr(10) & ' ('&Date([Date],'DD-MMM')&')', ', ')
Note: I haven't tested this so can't say for 100% that my syntax is correct. The sample data you provided does not have Date Type or Reference Only fields.
Dimension 1 (Model): Model
Dimension 2 (Month): =MonthName(MonthStart(Date))
Expression: =concat(Stage & ' (' & Date & ')', ', ')
I love where you are going with this.
However, my dates are coming in through a date bridge so I have to build a set analysis to select the desired date out of the available selections.
So the first part of the expression is like the below:
Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage,',')
Any thoughts on how to do that?
So the concat/set analysis that you have is working? You should just be able to add my extra logic to it (and leave the dimensions the same):
Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage & ' (' & Date & ')', ', ')
I tried using it and I am having a challenge with the end part because the date is Launch Date and I can only choose it using Set Analysis, so how would I build the '& Launch Date' part...
So I was able to build out the expression and it is working, temporarily lol.
If( Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage,',')<0,'',
Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage,',')
&chr(10)
&'('&Date( Only({$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>}[Date]),'DD-MMM')&')')
But the issue I am having is when the dates are not on the same day, it just shows blank () for the dates.
Any idea why?
It's because you're using the Only() function which returns NULL if you have more than one value.
You need to include the Stage and the Dates in the same concat:
If( Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage,',')<0,'',
Concat( {$<[Date Type]={'Launch Dates'},Reference_Only={'No'}>} DISTINCT Stage & chr(10) & ' ('&Date([Date],'DD-MMM')&')', ', ')
Note: I haven't tested this so can't say for 100% that my syntax is correct. The sample data you provided does not have Date Type or Reference Only fields.
Can I be honest?
I was doubting that this would work, but you are a miracle worker, I have worked on this for who knows how long and I am done.
Thank you so much Nicole, and thanks for the prompt responses. Much kudos