Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Chart with Date difference in days

Hi there,

I have bunch of data (customers) that contain the date that they where last updated (changed address, password etc etc). I also have another table that displays the transcactions of these customers( with a date field of each transaction). i want to create two bar charts that will show me how many of my records have a date difference in days between today and the date that i mentioned before. The records have a unique key but have a one to many relationship. (One customer, many transactions records).

In the first I want to diplay the count of the customers that have a date of update:

UP TP 2 DAYS
UP TP 7 DAYS
UP TP 30 DAYS
UP TP 90 DAYS
UP TP 180 DAYS
UP TP 365 DAYS
UP TP MAX DAYS
NO ACTIVITY

In the second i want to display the count of customers that have a latest date of transaction:

UP TP 2 DAYS
UP TP 7 DAYS
UP TP 30 DAYS
UP TP 90 DAYS
UP TP 180 DAYS
UP TP 365 DAYS
UP TP MAX DAYS
NO ACTIVITY

Thank you in advance guys!

1 Solution

Accepted Solutions
Not applicable

Re: Chart with Date difference in days

Hello!

Here's the load script that may help you, by attaching a flag to the max date for each customer:

Table1:

LOAD id,

     date,

     [value($)],

     id&date as Key

FROM

[test data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD id as id2,

Max(date) as MaxDate,

1 as flag,

id&Max(date) as Key2

Resident Table1

Group by id;

Left Join (Table1)

LOAD Key2 as Key,

flag

Resident Table2;

DROP Table Table2;

I also attach a qvd with the desired result on a table
Please let me know if it helped
Regards,

9 Replies
Not applicable

Re: Chart with Date difference in days

Hello

I would suggest using IntervalMatch for your problem, creating a table with your desired intervals
Hope it helps
Regards,

Not applicable

Re: Chart with Date difference in days

Hi there,

Thanks for the response.If you could give me an example, it would be very usefull.

Also how will i get the transaction with the most recent date from the transactions table and then use IntervalMatch to match it to one of my predefined categories based on the date difference?

Thanks again!

Not applicable

Re: Chart with Date difference in days

Actually, i solved the first part of my question without the IntervalMatch. I did it using an excel with the date difference and the value i want to display, and i created another field in my loading script that calculates the difference of days in days.

The second part is a bit difficult for me. How will i choose the most recent transaction for each customer?

Not applicable

Re: Chart with Date difference in days

Hello!
Can you send me sample qvw so I can have a better idea of your data and how to solve the problem?
To calculate the difference between the latest activity and the current day I would use the today() function
Regards,

Not applicable

Re: Chart with Date difference in days

Ok here it is a simple example.

As i said all i want is to find the most recent data from a series of data that have the same id.

Thank you.

Not applicable

Re: Chart with Date difference in days

How's the finished chart supposed or table supposed to be with your current data?
I am having a hard time trying to understand the end goal and maybe that will help me find a solution
Regards

Not applicable

Re: Chart with Date difference in days

Hi,

I think the qvw i attached is self explanatory. I will include an excel file with the initial test data and the desired results. Kep in mind i just want a way to get all the transactions for eacd customer (ID) at the most recent date for each customer purchase.

For example if customer with ID=1 has 10 transactions in his transactions history and the date of his most recent transaction is August 4th, then for this user (for ID=1 ONLY) i want to get only the transactions for this date. It may be just one transaction or many . It does not matter.

Now if customer with ID=2 has his most recent transaction at July 28th i want to get all of his transactions (for ID=2 ONLY) at July 28th. One or many it does not matter.

Thank you.

Not applicable

Re: Chart with Date difference in days

Hello!

Here's the load script that may help you, by attaching a flag to the max date for each customer:

Table1:

LOAD id,

     date,

     [value($)],

     id&date as Key

FROM

[test data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD id as id2,

Max(date) as MaxDate,

1 as flag,

id&Max(date) as Key2

Resident Table1

Group by id;

Left Join (Table1)

LOAD Key2 as Key,

flag

Resident Table2;

DROP Table Table2;

I also attach a qvd with the desired result on a table
Please let me know if it helped
Regards,

Not applicable

Re: Chart with Date difference in days

Hi there, thanks for your answer. It works fine!!!

No i want to create a table that shows the number of customers per number of transactions in a specified date.

For example if i choose August 6 as my date, how many Distinct users have 0 transactions for that date, how many 1 transaction, how many 2 transactions , etc. etc.?

Thanks.

Community Browser