Concat whit a date statement?

[sebastian_fager]

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)

[sunny_talwar · Accepted solution]

How about this:

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

[sunny_talwar]

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]

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]

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

[sunny_talwar]

Yes, I would expect this to work:

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

[sunny_talwar]

May be sort your chart using this expression:

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

[sebastian_fager]

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

[sunny_talwar]

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