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

[#2]

I have literally just asked the same question in another thread, I am not certain that Set Analysis is going to be the right tool for us...

I could get Only to work since it was specific, Min and Max to cover ranges, I have been ripping my hair out with...

[#3]

Hi,

Apparently, u can't do it.

Look this document : http://community.qlik.com/servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Pre...

I had the same question before and solved it by "if" like u did. Some colleague answer the same.

Regards.

[#4]

Hi again,

Did you delete your previous message ?

I join the test.qvw with my research. AS you can see, the set analysis limite is cross when we work with to much dimension.

Maybe concatenate date and type ? Hard way to solve problem ...

If isn't suffisant ?

[RSvebeck]

Hi.

Yes, I deleted my previous post which was about changing the datamodel as a optional solution request, since I realized that it will only solve the problem in my example - but in my real world actual data it will not be possible to change the datamodel, since I have not 3 types but 1000+ types, and one single transaction can belong to many types, so I came to the conclusion that solving this by changing the datamodel is not what I want, I need a solition with SET.

I look at your contribution in the test.qvw and as I understand, none of the set expressions give the same numbers as the IF expression. The "Foreced Type" way is probably in the right direction, but I don't understand why that example shows numbers for type a and C, it should only display numbers for B?
However - the forced type solution will not be a approach for my "real data" since I can't write one expression for each type when I have thousands of Types.

("Types" are actually contracts in my real world data.

I will keep investigating for a solution. So lets keep posting here until we finally give up or finaly solve it! Thanks for your feedback!

Regards, Robert

[#6]

Daisuke is right...

If you check the Set analysis white paper, it clear says that the Set Expression is calculated only once per chart. Not for each dimension. So although you are referencing the "Type" as a dimension, the Set expression will not evaluate relevant values of only(ValidFrom) in the modifier clause for each row of Type. It will just find that out once per chart.

[#7]

but in my real world actual data it will not be possible to change the datamodel, since I have not 3 types but 1000+ types

I was "worry" about that. Change Data model will be impossible cause of data quantities

but I don't understand why that example shows numbers for type a and C, it should only display numbers for B?

No cause set analysis are looking for min/max valid date for all type. not only for Type (per line).

That why after, I did some test about forced Type without solution ...

Regards, I keep one eye here !

[RSvebeck]

I'm getting more and more confused...

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

Page "Modifiers - Examples"

sum({$<OrderDate = DeliveryDate>} Sales)

"Returns the sales for the current selection where OrderDate = DeliveryDate."

Because if DeliveryDate is not depending on the dimension, it could be all or any delivery date?

I must be misunderstanding something here....

[RSvebeck]

Hmm, it must mean that this sum({$<OrderDate = DeliveryDate>} Sales) is not working in a table chart with dimensions, it will only work in dimensionless charts and textboxes etc then. I guess...

[vijay_iitkgp]

Why dont you do it at back end. You can use Intervalmacth to find candidates for bonus and front end only use simple expression.

Regards

Vijay