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: 
Not applicable

Getting range of dates grouped by Year and month upon selection

Problem:

Get Average cost of (a) product model(s).

Formula: (Total cost of claims in a month) / (Total number of installation of the model for last 24 month period)

Given: Total cost of claims

Application Detail:

Warranty Application Database:

User selects Year and Month which relates to the dates in ClaimDate field for a particular product Model. There is another set of dates: Install Dates for the same product Model. But the catch is that there will be multiple install dates for each set of product Models. For example, we have 1000 product A and the Installation date ranges from Jan 01 2000 to Dec 31 2009 for each of the models.

relation for these two table is the Installation number.

Claim: Install# (many)------------------------->(1) Install

If I were to get the Average cost of (a) product model(s) using the formula below

(Total cost claims in a month) / (Total number of installation of the model for last 24 month period)

how would I get the grouping done for the range of install date dating back 24 months upon choosing month(s) and Year(s) relating to Claim Date.

Choosing options would be like:

Claim date Year: 2008, and 2010

Claim Date Month: Jan, April

This should give me a result in chart like:

error loading image

Any help is appreciated. Thanks.

-Samir

4 Replies
johnw
Champion III
Champion III

Maybe something like this?

sum(CostClaims)/count({<InstallDate={"<=$(=ClaimDate) >=$(=date(addmonths(ClaimDate,-24)))"}>} distinct Install)

Not applicable
Author

Thanks John for the quick reply. However, I don't think this worked but I think the solution would be close to the above.

Can you explain how the above code give you the count of total number of installation with respect to each selected model(s)? Because, the Model is not the link between the two tables but the installation number is, how is the above code going to give you the count for the model selected? Would it not give you all the installation date of all the models installed with the the 24 months time period?

Also, what is Install on the code above where you said at the very end of the statement: ".......... distinct Install"?

Let me try and explain how I understood your code before I came to the above conclusion.

I'm assuming everything within <> tag is an array and everything within {} brackets is a record set. If this is correct, then there is no one to one mapping of the InstallDate to the ClaimDate, i.e., there might not be a ClaimDate but there might be an install date on a particular date. For example: a product model B was installed on jan 1st 2008, but there was no caim for any of the models on that date. Then the installation count is ommited in this case. Please see the Table view of the file below. (You can ignore the two related island table)

Your resposnse is highly appriciated. Thanks.

-Samir

johnw
Champion III
Champion III

Since you said the formula was for the average cost of a product model or models, I assumed that the product model was either selected or a dimension of the table. Looks like the model in question is the "Model" field in the "Warranty_NewInstalls" table? It doesn't matter that it's not the field you connect on. QlikView understands that that Model is associated with these SARGs (whatever that is), which in turn connects to these claims. That's just basic QlikView associative logic.

Or did you think the set was overriding the model in some way and making us get all models? It isn't. Sets by default start from a base of your current selections, and expressions by default are analyzed in context of the row of the chart. So when you're on the row for model ABC, you will only get claims and installs associated with model ABC.

Distinct Install is because you asked for "total number of installation". I assumed you would have an installation table somewhere keyed by a hopefully unique installation ID, which I would call "Install" if it were my table. If I'm reading your data structure correctly, is SARG your install number? Then you'd count distinct SARG instead of distinct Install.

The <> brackets aren't exactly an array, more like a set of records with an install date in the specified range. Everything within the {} brackets is also a record set. You can specify multiple sets within the {} brackets, with operators like union or intersection, but in this case I just have a single set. So it's the set of one set. That set isn't a complete specification, but rather a modification based on your current selections. It is taking your current selections and then overriding them. Specifically, it is overriding any InstallDate selections, replacing them with a 24 month range based on the ClaimDate. What I DID fail to do, and I think you're pointing out, is override the ClaimDate as well. I should have told the set to ignore it completely (other than for setting the InstallDate). That gives us something like this so far:

sum(CostClaims)/count({<ClaimDate=,InstallDate={"<=$(=ClaimDate) >=$(=date(addmonths(ClaimDate,-24)))"}>} distinct SARG)

Then it's a matter of plugging in the right field names. I think this:

sum(Cost)/count({<WarrantyClaimDate=,INSTD={"<=$(=WarrantyClaimDate) >=$(=date(addmonths(WarrantyClaimDate,-24)))"}>} distinct SARG)

Though since WarrantyClaimDate is in a calendar, it's probably best to tell it to ignore all fields in the calendar, not just the date, since they might select the Year instead of a bunch of dates:

sum(Cost)/count({<WarrantyClaimDate=,Year=,Month=,Week=,Weekday=,Quarter=,Days=,INSTD={"<=$(=WarrantyClaimDate) >=$(=date(addmonths(WarrantyClaimDate,-24)))"}>} distinct SARG)

Chances are still low that that's the EXACT expression you need, but that's the idea.

Not applicable
Author

Hi John,

I really appriciate your help on this one. But the above code does not do the job. Like you said, its not the exact solution, but the idea is similar.

One this note, if I can understand correctly, your explaination above for the record set would say:

get all the install dates (INSTD) = (all the warrantyClaimDate >= previous 24 months of the warrantyClaimDate chosen.) per model

(Yes, Model(s) are chosen/selected in this case)

However, two things come in to my mind, and please feel free to correct me if I'm wrong in interpreting the clickview code:

1. When you select a year AND a month, this will pull all the WarrantyClaimDate in the warranty_DealerClaim table which in turn will only select a set of SARG in Warranty_DealerClaim. Now if you select a Model in the Warranty_NewInstall table, this will be and intersection of WarrantyClaimDate AND Warranty_DealerClaim.SARC AND Model.

Will the above code ignore all the INSTD date from the above Intersection and re-count all the SARG (unique in the Warranty_NewInstall table) for just last 24 months from the Year and Month selected for WarrantyClaimDate ?

2. I'm still not sure if (INSTD) = (all the warrantyClaimDate >=.................) will work becuase, like I explained in my previous post, not all INSTD is one to one with WarranyClaimDate.

Let me put this problem in a different way if above idea is not working. Is there a way we can query qvd file from the expression within the application? That is basically what we are trying to do, correct? Just count the number of installation for a date range in the Warranty_NewInstall for a selected model, group by Model?

Again, appriciate your help.

-Samir