Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sebastian_fager
New Contributor II

Concat whit a date statement?

Hi,

Am new at qlik and are self learn, so please bear with me!

I have a problem to merge branch names to one cell whit a date statement in the set analys.

I want to merge all branch names that have bought the product in the past 6 months.

Feels like i have tried everything, this is the closet:

if(date(Orderdate) > date(AddMonths(today(),-6)), concat(DISTINCT branchname, ','), '')

This works in the table if i have the order date as a column. But then all the values is shown. So i don't want to display the order date in the table.

Initial state:

Product          Branch     Orderdate

A                    AX,IC         2016-05-02

A                    FO             2016-07-02

B                    AX             2016-05-22

B                    IC              2016-06-13

I want this:

Product         Branch

A                    AX,FO,IC

B                    AX,IC

(and non Orderdate)

1 Solution

Accepted Solutions
MVP
MVP

Re: Concat whit a date statement?

How about this:


Concat(DISTINCT if(date(Orderdate) > date(AddMonths(today(),-6)), branchname), ',')

8 Replies
MVP
MVP

Re: Concat whit a date statement?

How about this:


Concat(DISTINCT if(date(Orderdate) > date(AddMonths(today(),-6)), branchname), ',')

MVP
MVP

Re: Concat whit a date statement?

Or better still, this:

Concat(DISTINCT {<Orderdate = {"$(='>' & Date(AddMonths(Today(), -6), 'OrderdateFormatHere'))"}>} branchname, ',')

Here OrderdateFormatHere is a placeholder for the format of your Orderdate field

sebastian_fager
New Contributor II

Re: Concat whit a date statement?

Wow, this one works great!

Do you also know how to sort the branch name in the cell by number of bought products?

Now it's sorting by alphaphete.

sebastian_fager
New Contributor II

Re: Concat whit a date statement?

Didn't get this to work, just curious.. My format is 2016-08-02, should i then put 'YYYY-MM-DD' ?

MVP
MVP

Re: Concat whit a date statement?

Yes, I would expect this to work:

Concat(DISTINCT {<Orderdate = {"$(='>' & Date(AddMonths(Today(), -6), 'YYYY-MM-DD'))"}>}branchname, ',')

MVP
MVP

Re: Concat whit a date statement?

May be sort your chart using this expression:

Count(DISTINCT if(date(Orderdate) > date(AddMonths(today(),-6)), branchname))

sebastian_fager
New Contributor II

Re: Concat whit a date statement?

I don't really understand..

I have a tabell, and i want to sort the branch name inside the cell,

Now it's: AX, BC, CA

But if:

AX buys 7

BC buys 8

CA buys 4

The the order in the cell should be BC, AX, CA

MVP
MVP

Re: Concat whit a date statement?

May be like this:

Concat(DISTINCT if(date(Orderdate) > date(AddMonths(today(),-6)), branchname), ',', -Aggr(Count(Branch), Product))

Not sure if this will work, but I can take a deeper look, if you are able to provide a sample to play around with