Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

Using Dual and Concatenate in a Pivot Chart Expression

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. 

 

Labels (3)
1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

7 Replies
Nicole-Smith

Dimension 1 (Model): Model

Dimension 2 (Month): =MonthName(MonthStart(Date))

Expression: =concat(Stage & ' (' & Date & ')', ', ')

ZoeM
Specialist
Specialist
Author

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?

 

Nicole-Smith

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 & ')', ', ')

ZoeM
Specialist
Specialist
Author

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

ZoeM
Specialist
Specialist
Author

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?

 

Nicole-Smith

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.

ZoeM
Specialist
Specialist
Author

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