Chart with Date difference in days

[#1]

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!

[#2]

Hello

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

[#3]

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!

[#4]

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?

[#5]

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,

[#6]

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.

[#7]

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

[#8]

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.

[#9 · Accepted solution]

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,

[#10]

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.