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]

This solution, when you select single date, takes all month sale and 3 next months claim.

You may calculate yearmonth during reload and then let user select this yearmonth.

You will need different concept only in the case, if you user would like to select at the same time dates from few different months.

regards

Darek

[#3]

Dear Dariusz,

It does not.

I used your script and expression but it works when I select a particular date and results are shown on the basis of the selling happened on that particular date.

It does take data for next three month of claim date but only for a particular Sale Date & not the whole month.

When I select a month, the expression isnt evaluated until and unless a particular date is selected.

I am not able to crack it.

Please let me know if you can find any solution.

Thanks & Regards,

[#4]

Sarang,

maybe it is some date format issue.

Please put this expression into straight table.

Select one date.

Then in the header you should see full expression (with evaluated date values in set analysis).

It should be something like this:

count({<SaleDate={">=2014-01-01<= 2014-01-31"},ClaimDate={">=2014-02-01<=2014-04-30 "}>} SaleDate)

let me know if it is.

[#5]

Dear Dariusz,

You are absolutely right.

The output in the table shows same as you suggested, but actually when it evaluates - it considers only that sales which happened on the selected date and not the whole month.

Even I checked the date format, its same as "DD/MM/YYYY".

Ideally the expression suggested by you should evaluate for the whole month but actually its evaluating the selected date only.

If possible, can you please suggest an expression which considers whole month and on that basis, next three months of claim date.

Thanks & Regards,

[#6]

Dear Dariusz,

Using your expression, I have made some changes as follows :-

=count({<SMonth={'$(vSlctdMonth)'},SYear={'$(vSlctdYear)'},saledate=,Flag={'1'} ,ClaimDate={">=$(=MonthsStart( 1, SaleDate,1))<=$(=MonthsEnd( 1, SaleDate,3))"}>} PVin)

1. I am passing the selected year & month through variables vSlctdMonth & vSlctdYear resp.

2. I have created a flag which give value "1" when UniqueID matches UID.

3. I am counting the no. of UID

The above expression gives me right answer for few months (need to validate the data fully).

The problem is ...... after selecting year & month - I have to specifically select a particular date also and then only the expression works.

It may seem silly but the business user does not want more than 2 clicks and here we have to do three clicks.

Let me know if you can find out a solution.

Sorry for troubling you so much.

Many thanks for your help.

[#7]

Set analysis on date field forces selection defined in this set expression

on that field. But maybe you also have somewhere else some selections on

another fields?

15-05-2014 07:00 użytkownik "Sarang Mehta" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Control Next three

months of 2nd Date using 1st Date

reply from Sarang Mehta<http://community.qlik.com/people/sarangmehta?et=watches.email.thread>in *App

Development* - View the full discussion<http://community.qlik.com/message/528737?et=watches.email.thread#528737>

[#8]

Yes Dariusz.

I do have other 2 selections also which get disturbed as I have to select a particular date forcefully.

Hence I was thinking of passing Selected Month value through variable "vSlctdMonth" in MonthStart function instead of SaleDate but no success.

[#9]

It is of course possible with only year/month selection. You have to load

yearmonth periods as dimension with consequitive numbers. Havning those

numbers you will be able to do calculations on it and use results in set

analysis preparing 3 next periods range.

Good Luck!

Let me know If you need more help.

Regards

Darek

15-05-2014 11:30 użytkownik "Sarang Mehta" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Control Next three

months of 2nd Date using 1st Date

reply from Sarang Mehta<http://community.qlik.com/people/sarangmehta?et=watches.email.thread>in *App

Development* - View the full discussion<http://community.qlik.com/message/528986?et=watches.email.thread#528986>

[#10]

Dariusz,

I tried Addmonths function also but it works when a particular date is passed (like monthstart function).

I am not able to figure out the code to use it in script & front end.

Regards,

[#11]

Yes, you are right. Addmonths need date:)

You have 2 possibilities:

Prepare single date from period selected by user, using for instance

min(date)

Use period number. Then you may use expression: periodnumber + 3 instead of

addmonth.

17-05-2014 12:50 użytkownik "Sarang Mehta" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Control Next three

months of 2nd Date using 1st Date

reply from Sarang Mehta<http://community.qlik.com/people/sarangmehta?et=watches.email.thread>in *App

Development* - View the full discussion<http://community.qlik.com/message/530258?et=watches.email.thread#530258>