Two conditions have to be fulfilled!

[#1]

Hello everybody,

I am a beginner with QlikView and after trying my first analysis I have got a lot of questions.

But know I would like to start with just one question.

In one of my analysis I would like to select all people which made revenue in 2013 and 2014. (I don´t want to have any person in my list who only made revenue in one of these both years!)

Right know I have the Problem, that if I mark 2013 an 2014 in the table 'Year' it shows me also people which have made revenues in only one of these both years.

Do I have any possibilities to get only the persons with a revenue in both years? Does maybe exist an 'and' order or do I have to work with a join?

It would be great, if someone of you could help me with my problem.

Best regards

Max

[jaimeaguilar]

Hi,

the expression you need depends on the fields in your data model as well as the fields that you use as filters in the front-end. However the expression could be something like this:

sum({$<Year={'$(=Year(today()-1))'}>*<Year={'$(=Year(addyears(today()-1,-1)))'}} Revenue)

the Year(today()) and the Year(addyears(today(),-1)) returns you the year of today and the year-1 of today respectively. This way the selection done by set analysis will be dynamic. Then the * is used to make an intersection between both sets, so the expression will return you the people that had revenue in 2014 AND 2013. If you put the years in the same set like this: Year = {2013,2014} then it will work as a union/or.

Also it is important to note that probably you'll need to ignore selections from any field that may cause conflict with set analysis (Especially date fields like month, day, week, etc). you can achieve it by adding those fields like this:

{$<Month=,Date, Week=, Year={2014}>}

Also if you're searching for strings you need to use single/double quotes but if you're looking for number (just in static search) you don't need any quotes, that's why 2014 in the example doesn't have quotes.

Set analysis syntaxis may be confusing but it is just matter of practice,

regards

[#3]

Thank you for your really fast answer!

Can you tell me where I have to implement this order? Do I have to implement this order in the script or in the diargam?

best regards

[SunilChauhan]

you could you simply

in dimension take personname

Temp:

Load peoplename,

*

from table where Year=2014

inner join

Load peoplename,

*

from table where Year=2013

might helps

[VishalWaghole · Accepted solution]

Hi Max,

Try this,

=Sum({<PersonId=p({1<Year={2013}>})*p({1<Year={2014}>}PersonId)>}Revenue)

Hope this will solve your problem.

-- Regards,

Vishal Waghole

[jaimeaguilar]

Set analysis works only in front-end/graphics

regards

[#7]

Hey Vishal Waghole,

thanks for your help!

It works:)

best regards

Max