Yet another between dates SET question...

[RSvebeck]

Hi..

I 've been browsing the forum for a solution to my simple question, but I have not found a working solution. Perhaps it is too simple...?

What I am looking for is a solution where I have sales data and contract dates to be able to calculate a possible bonus. If I have transactions within the contract dates, a bonus shold be paid out.

I have solved this with "if" expressions, but now I need it with a SET expression also.

I have made an example with two small tables:

Transactions:
Date       Value Type
2012-07-17 302   A
2010-08-21 369   B
2010-08-10 984   C
2011-02-25 869   A
2011-06-17 637   B
...etc

Contracts:
Type  ValidFrom  ValidTo
A     2011-01-01 2011-12-31
B     2012-01-01 2012-12-31
C     2011-07-01 2012-07-01

Now, I want to make a table chart showing two sums of the values for A,B and C.

Sum A: "Total value"
Sum B: "Total valid value"

My problem here is the expression for Sum B.

This works:

sum(if(Date >= ValidFrom and Date <= ValidTo, Value))

But I want to use SET Analysis, and this does not work:

sum( {$ <Date = {">= $(=only(ValidFrom)) <= $(=only(ValidTo))"}>} Value)

The reason seems to be that Qlikview ignors my dimensions in my chart (Dimension is Type)  so that only(ValidFrom) and only(ValidTo) fails.

Once I get this to work, I also need to be able to select one or several months, or days in the report and it will calculate the values for that period which is also included in the range of valid dates.

So If I select November 2012, only values from November 2012 and from type B will be displayed, since type A and C are not within their valid date range.

Perhaps my explenation is not easy to understand, so I also made a attached example.

Hope someone can help me. It seems simple, but ...

//Robert

[RSvebeck]

Yes, in this example it would be posible.

But the background is that I have a transaction table with sales data (customers, items, dates, values). Then there are contract tables where we specify when the contract is valid, for which items and for which customer ID's they are valid.

The sales data consists of 40,0000,000 records, and there are around 4,000 contracts.  So one single sales data row can be a valid row for many many (sometimes all) contracts, and one single contract is often valid for 10,000 items and 10 customer ID's. A contract duration spans from beeing valid only one month up to several years. And above that, a customer ID can appear on several contracts...

I could run interval match for a few contracts perhaps, but not for all contracts?  - I  think...or am I wrong?

The goal is to get the total sales any given month for all valid contracts to be able to calculate a kickback bonus per customer based on that.

[#3]

I'm getting more and more confused...

So what does this example, taken from that PDF mentioned above,
actually calulate then?

Page "Modifiers - Examples"

for the current selection ! In your case, we dont select anything. If my test.qvw, the table partially work when I select a Type.

Problem is to "link" Type with "ValidFrom/To" Date without selection. And we can't cause Set Analysis work with a set of information.

In a table, 1 line isn't egal to a set. So QlikView can't determined Date set for A, B, C. That why he calculated the sum(Value) for all Type in spit of each line.

[jonathandienst]

Hi

If each bonus calculation is not dependant on any front-end selections, I would strongly recommend doing these calculations in the load script. This calculation may be time consuming, but it is far better that the time be spent in the back-end, which should signiificantly the front-end, user experience.

Operations like interval matching perform very well in the back end, and assuming you have a suitable PC or server, there should be no reason not to do all contracts. But only testing this will prove whether this is the right approach or not.

Regards

Jonathan

[RSvebeck]

Yeah, but if the users don't make a correct selection, they won't get any useful data from this expression. We must also then make sure that they select data that limits out all variants of DeliveryDate. If the DeliveryDate field has more than one possible value, it will fail. And doing a {1} kind of set with this solution will surely fail.

Anyhow, I think I am slowly beginning to understand how this works and I guess I must realize that my task is not solvable with SET.... afterall.... I was hoping for smart solutions with adhocvariables or sometihng like that, but as I understand it now, this will not work either. I must somehow go back to the drawingboard here...

Best Regards, Robert

[#6]

In fact, u can solve this problem thank to set analysis. Keep if solution in spit of performance desagreamant. Or force a Type selection ? I explain.

U can use "my" set analysis (when he work by Type selection) and add a condition to your graphics. First property sheet, u can set a condition when u want display the graphics.

See attachement.

Good luck.