Set analysis intersection

[#1]

Hello.

I've tried to follow related tips shown on this forum (e.g.http://community.qlik.com/forums/p/27497/106731.aspx#106731), but unfortunately I can't get what I need.

I need to define a set analysis that sums all the sales between 01/01/2009 (this date will be dinamic, but at the moment it's fine to have it static) and exactly one year before the selected date.
For example, I select 25/05/2010 and then I want to have the total sales between 01/01/2009 and 25/05/2009.

I've used this:

=sum({$<doc_date = {"<=$(=makedate(year(only(doc_date)),month(only(doc_date)),day(only(doc_date)))) >=$(='01/01/2009')"}>} sales)

If this work without the last condition, it doesn't with >=$(='01/01/2009') specifies. I've tried using the intersection (*) operation, but I wan't able to get it work.

Could you please help me?

Cheers,
Cortez

[#2]

Hi Cortez, the problem I see is that when you use mathematical comparision operators such as sum( {$<Date = {"<=A>=B"}>} Val) the numbers in this case A and B have to be numbers. To accomplish this you have to cast the dates to numeric data, similar to the following:

=sum({$<doc_date = {"<=$(#=makedate(year(only(doc_date)),month(only(doc_date)),day(only(doc_date)))) >=$(#=makedate(2009,1,1))"}>} sales)

Regards

[#3]

Thank you Ivan.

I've tried your solution, but with the # before it doesn't work neither the formula which used to work:

=sum({$<date_doc = {"<=$(=makedate(year(only(date_doc))-1,month(only(date_doc)),day(only(date_doc))))"}>} isales)

This formula works correctly, but it doesn't as soon as I specify the second condition.

[#4]

Hi Luca,

This is the expression you can use for set analysis intersection.

Count({$<DebtNo-=P({1<YM={"$(vLast1YearMonth)"},Year=,Quarter=,MonthName=,BUCKER={'1'}>} DebtNo),Year=,Quarter=,MonthName=,BUCKER={'1'},YM={"$(vCurrentyearmonth)"}>} DebtNo)

Consider this one as refrence and now you can make few changes in your expression that will work.

P& E IS USED IN SET ANALYSIS FOR INTERSECTION AND UNION KIND OF CALCULATIONS...

ALSO YOU CAN HAVE A LOOK AT HELP (SET ANALYSIS)..........

Regards,

Dushyant

[jonathandienst]

Hi

You may be having a problem with the date format. I assume that doc_date contains a date value and I assume that it is in the default date format. What is your default date format? I suspect that it is something other than DD/MM/YYYY.

Makedate will return the correct format, but the string epxression will not.

Try just the second expression like this:

=sum({$<doc_date = {">=$(=Date#('01/01/2009', 'DD/MM/YYYY'))"}>} sales)

Hopefully that will put you on the path to solving the problem.

Jonathan

[#6 · Accepted solution]

Thank you all for your help!

I found that the problem was somewhere in the set analysis sintax (fixed once I started writing it back from scratch), and not related to data conversion into numbers. Taking the two conditions independently, they work, but together they don't, thus that must have been something with the operators or with the sintax: divide et impera is always a good way to solve problems! Here the correct formulae:

Total sales previous year, until the selected date:

=sum({$<doc_date = {"<=$(=makedate(year(only(doc_date))-1,month(only(doc_date)),day(only(doc_date)))) >=$(=makedate(year(only(doc_date))-1,1,1))"}>} sales)

Total sales current year, until the selected date:

=sum({$<doc_date = {"<=$(=doc_date) >=$(=makedate(year(only(doc_date)),1,1))"}>} sales)

doc_date is in the format DD/MM/YYYY.

I hope this solution might help someone else who is stuck in my same situation.

Ciao and thanks again,
Luca