topic Re: Dual Function in Pivot Table in QlikView

Dual Function in Pivot Table

ZoeM — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik community.

I have scoured the forums looking for information on how you can use the Dual Function in a expression in a Pivot table and couldnt ifnd anything.

Can any of you great experts help me with this?

I want to show the Item and Order date in one cell within a pivot table, please see attached.

And is there any way I can hide the 'Month' label? And just have the months showing as is.

Thanks so much!

Zoe-

Re: Dual Function in Pivot Table

sunny_talwar — Fri, 15 Jun 2018 14:04:58 GMT

You don't need dual function for this... you can try this

Item & ' ' & [Order Date]

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 14:43:23 GMT

Amazing!

That is exactly what I was hoping for.

As always, thanks Sunny!

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 15:12:10 GMT

So Mexico and Uruguay bought multiple items on the same date. How can I show these items in the same cell and just one date?

Re: Dual Function in Pivot Table

sunny_talwar — Fri, 15 Jun 2018 15:13:38 GMT

You can use Concat

Concat(DISTINCT Item & ' ' & [Order Date])

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 15:21:58 GMT

The date is duplicated for each Order Item. Is it possible to have only one date? Since they are the same...

Re: Dual Function in Pivot Table

sunny_talwar — Fri, 15 Jun 2018 15:22:44 GMT

Try this then

Concat(DISTINCT Item) & ' ' & [Order Date]

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 15:27:58 GMT

For the data set I am working in, I am using Set Analysis where the date field is coming into the QVW via a Date Key. So I have to distinguish the date field as below:

Only({$<[Date Type]={'VPP Dates'}>}[Date])

When I write my expression to concatenate the order Item and Purchase date I am getting an error:

=Concat(DISTINCT 9Order Item]&CHR(10)&

Only({$<[Date Type]={'VPP Dates'}>}[Date]))

What could be the issue?

Re: Dual Function in Pivot Table

sunny_talwar — Fri, 15 Jun 2018 15:33:01 GMT

what is 9Order Item]? Typo? Also, a missing parenthesis... try this

=Concat(DISTINCT [Order Item])

&CHR(10)&

Only({$<[Date Type]={'VPP Dates'}>}[Date])

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 15:33:38 GMT

I saw what it was...I forgot a parenthesis.

Your formula works, however there is no space between the Items:

ScrapersTrophies

Any way to separate?

Re: Dual Function in Pivot Table

sunny_talwar — Fri, 15 Jun 2018 15:37:48 GMT

Yes.. you can use comma or anything you would like....

=Concat(DISTINCT [Order Item], ', ')

&CHR(10)&

Only({$<[Date Type]={'VPP Dates'}>}[Date]))

Re: Dual Function in Pivot Table

ZoeM — Fri, 15 Jun 2018 15:43:23 GMT

Thanks Sunny! Works like a charm...