Control Next three months of 2nd Date using 1st Date

[#1]

Hi All,

PFA two excel sheet.

E1 - contains sales data of a product.

       UniqueID filed is the unique no assigned to a particular sale.

       SaleDate is the date of sale occurring.

U2- contains data of complains that are been sold.

       ProductClaimNo - is the unique ID assigned when the  complaint is registered.

       ClaimDate :- the date when complaint is registered.

        UID :- its the foreign key to E1 table, its the same UniquID which has been sold and now a complaint is registered for it.

Now as per E1 data, all the sale has been done in Jan - 14.

Now my requirement is :-

If I select Jan - 14 from SaleDate from E1, I should know all the complaints registered in next 3 months from ClaimDate

i.e. Feb, Mar & Apr.

Out of all those complaints in next three months of ClaimDate, only those complaints should be shown whose SaleDate from E1 is in Jan-14.

Eg:- 20 products have been sold in Jan - 14 (according to E1)

        Total 20 Complaints have been registered in month of Feb, Mar, Apr & May.

        Out of those 20, 16 complaints have been registered in the month of Feb,Mar & Apr (next three months) for those products                 which were sold in Jan - 14 .

       So when I select Jan-14  (or any other month)on the basis of saledate, i should get only those complaints whose UniqueID                  matches UID in next three months.

I have tried left joining, concatenating the data but it didn't work out.

Please Help.

Thanks in advance.

[#2]

I think, you should:

1. change field name of UID or UniqueID, to have both with the same name. You will have key.

2. decide if you want to select SellDate or maybe year_month of it.

3. decide if you need mastercalendar

4. use set analysis in your expression

should be rather easy!

regards

Darek

[#3]

Dear Dariusz,

Thanks for the suggestion.

Different approaches tried by me :-

1. Concatenated data by changing UID to UniqueID and created a flag to distinguish between saledate & claimdate.

    I hope you have understood that using saledate, I need to control the claimdate.

    Hence I need the master calender.

    Also I need to create a flag to find out which UniqueID matches UID in the consecutive months after selecting the selling month.

2. Left join ProductComplaints data to Sale Data.

Note :- Using set analysis, I am not able to control the claim date/month after selecting select date / month

Either I am able to control the dates or control the flag  to find out which UniqueID matches UID.

Not able to satisfy both the conditions at a time. If I try, it results into loop.

Thanks & Regards,

[#4]

I will try to prepare example, but let me know - if for exaple you select saledate = 15-01-2014, you would like to have:

all rows with salesdate from january-2014 and   ClaimDate in feb,mar,apr-2014?

or maybe:

- rows with saledate=15-01-2014 and ClaimDate in feb,mar,apr-2014?

or maybe:

- rows with saledate=15-01-2014 and ClaimDate from 16-01 to 15.04.2014 ??

regards

Darek

[#5]

Dear Dariusz,

Example :-

E1 table consists of Sales Data. Only Jan data is used for example.

If I select Jan - 2014 from sale date, You can see there are 20 products sold.

U2 contains 20 complaints data from Feb till May 2014.

Steps ;-

1. select jan-2014 on the basis of sale date -> you will find out that 20 products were sold in that particular month.

2. automatically Next three months i.e. feb, mar & april should be considered from claimdate -> you will find out that 15    complaints were registered.

3. Now out of those 15 complaints, we have to find out how many complaints are for the products which were sold in         month of jan-2014 (that we had selected on basis of saledate)

4. So you will come to know that out of 15, 13 complaints were registered for the products that were sold in jan - 2014.

   "Those 13 complaints are Yellow coloured rows in the U2 excel sheet "

PFA the excel sheets again with a bit of change.

Similarly, whatever month the user selects on the basis of saledate, from next three months(claim date) - complaints associated with those UniqueID that were sold in that selected month should be shown.

Thanks & Regards,

[#6]

try this:

count({<SaleDate={">=$(=MonthsStart(1,SaleDate,0))<=$(=MonthsEnd(1,SaleDate,0))"},ClaimDate={">=$(=MonthsStart( 1, SaleDate,1))<=$(=MonthsEnd( 1, SaleDate,3))"}>} SaleDate)

[Anonymous]

try this

where( date(ClaimDate) - date(SaleDate) <= 90 ) and date(ClaimDate) >= date(SaleDate)

[#8 · Accepted solution]

This expression gives result 12 (exactly as number of yellow rows in your xls U2 file)

It works with load script like this:

LOAD ProductClaimNo,
     ClaimDate,
     year(ClaimDate)&'-'&month(ClaimDate) as ym_claimd,
     UID
FROM
[http://community.qlik.com/servlet/JiveServlet/download/526437-105883/U2.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD SaleDate,
year(SaleDate)&'-'&month(SaleDate) as ym_saled,
     UniqueID as UID
FROM
[http://community.qlik.com/servlet/JiveServlet/download/526437-105908/E1.xlsx]
(ooxml, embedded labels, table is Sheet1);

I used this expression in chart with no dimension.

And select exactly one SaleDate value.

regards

Darek

[#9]

Dear Dariusz,

Many thanks for your efforts and solution.

It is working for a single date.

The business requirement is that it should work on selecting a month (i.e. all the dates of the moth should be considered) and not a single SaleDate value. Also the month logic will change when used fiscal year format as well as at the start and end of the year.

I will be transforming this SaleDate into fiscal year format.

More than one products would have been sold on the single SaleDate.

I am also trying to work that out.

Request you to help me on that if possible.

Regards,

[#10]

Thanks Venkata for the suggestion.

But the requirement is not on the basis of 90 days difference and also not a single date.

The whole month has to be considered.

Regards,