topic Re: Using Dual and Concatenate in a Pivot Chart Expression in QlikView

Using Dual and Concatenate in a Pivot Chart Expression

ZoeM — Sat, 16 Nov 2024 19:35:28 GMT

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.

Re: Using Dual and Concatenate in a Pivot Chart Expression

Nicole-Smith — Mon, 25 Nov 2019 16:21:39 GMT

Dimension 1 (Model): Model

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

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

Re: Using Dual and Concatenate in a Pivot Chart Expression

ZoeM — Mon, 25 Nov 2019 16:26:36 GMT

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?

Re: Using Dual and Concatenate in a Pivot Chart Expression

Nicole-Smith — Mon, 25 Nov 2019 16:37:18 GMT

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

Re: Using Dual and Concatenate in a Pivot Chart Expression

ZoeM — Mon, 25 Nov 2019 17:27:51 GMT

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

Re: Using Dual and Concatenate in a Pivot Chart Expression

ZoeM — Mon, 25 Nov 2019 18:37:09 GMT

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?

Re: Using Dual and Concatenate in a Pivot Chart Expression

Nicole-Smith — Mon, 25 Nov 2019 19:58:08 GMT

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.

Re: Using Dual and Concatenate in a Pivot Chart Expression

ZoeM — Tue, 26 Nov 2019 13:57:52 GMT

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