Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sebastian_fager
Contributor III
Contributor III

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
sunny_talwar

How about this:


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

View solution in original post

8 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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