Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

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. 

 

1 Solution

Accepted Solutions
Highlighted

Re: Using Dual and Concatenate in a Pivot Chart Expression

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
Highlighted

Re: Using Dual and Concatenate in a Pivot Chart Expression

Dimension 1 (Model): Model

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

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

Highlighted
Creator III
Creator III

Re: Using Dual and Concatenate in a Pivot Chart Expression

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?

 

Highlighted

Re: Using Dual and Concatenate in a Pivot Chart Expression

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

Highlighted
Creator III
Creator III

Re: Using Dual and Concatenate in a Pivot Chart Expression

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

Highlighted
Creator III
Creator III

Re: Using Dual and Concatenate in a Pivot Chart Expression

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?

 

Highlighted

Re: Using Dual and Concatenate in a Pivot Chart Expression

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

Highlighted
Creator III
Creator III

Re: Using Dual and Concatenate in a Pivot Chart Expression

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