topic From IF to Set Analysis (simple) in QlikView

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 10:38:20 GMT

Hi,

can anyone tell me what I am doing/thinking wrong in this case? I am trying to make a 30-60-90 split on our invoices, sorted by duedate. If i make it with IF's, it works fine, but when trying to write a set analysis, I am obviously doing something wrong.

I thought this


IF(RNREST<>0, IF(DD-RNFDAT<0, RNBELØ,0))

would transfer in to this:


SUM ({<RNREST= {'<> 0'}, [DD-RNFDAT]= {'< 0'}>} RNBELØ)

This should be really easy to get to work, right? Anyone..?

*newbee in love*

From IF to Set Analysis (simple)

Wed, 19 May 2010 11:20:09 GMT

Hi Anita,

You are almost there. Your code should be as follow.

SUM ({<RNREST -= {0}, [DD-RNFDAT]= {'< 0'}>} RNBELØ)

-Peterson

Re. :Re: From IF to Set Analysis (simple)

martin59 — Wed, 19 May 2010 11:24:32 GMT

Hi,

I think it should be :

SUM ({<RNREST -= {0}, [DD-RNFDAT]= {"<0"}>} RNBELØ)

Hope it helps you

Martin

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 11:47:00 GMT

No, it still doesn't work.. 🙂

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 11:53:14 GMT

I have now even tried to change my Norvegian Ø to O, in case this was part of my problem, but i still does not work.

My IF is working fine though...


IF(RNREST<>0,
IF(DD-RNFDAT<0, RNBELO, 0))

Please help.. 🙂

From IF to Set Analysis (simple)

Anonymous — Wed, 19 May 2010 11:54:43 GMT

You could try to split it up to see where the problem is ...

First try out

<blockquote><pre> SUM( {< RNREST -= {0} >} RNBELØ)

See if the correct items are taken (according to the set analysis part)

After this try out

SUM( {< [DD-RNFDAT] = {<0} >} RNBELØ)

See if the correct items are taken (according to the set analysis part)

I think the problem probably lays in the second one ... focus on the part which doesn't work ...

best regards,

Anita F.

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 12:27:36 GMT

Hi Anita,

You are correct, the problem lies in the "second" one.

I have tried to make a simplyfied report, showing my problem.

I would be more than happy if anyone could look at it 🙂

From IF to Set Analysis (simple)

Anonymous — Wed, 19 May 2010 12:56:43 GMT

Hi Anita,

Did some testing ... think this is what you need:

SUM( {< RNREST -= {0}, RNFDAT = {'>$(DD)'} >} RNBELO )

The RNFDAT should be larger than the DD as I understood ... DD - RNFDAT gives the number of days between the DD and RNFDAT ... < 0 this means RNFDAT should be counted ...

Hope this helps!!

regards,

Anita F.

From IF to Set Analysis (simple)

Wed, 19 May 2010 13:00:15 GMT

Hi,

I would not be able to open your application, since i have personel edition on my system.

So i have created a cample aplication and it works as i expected.

Pl check the attached application.

-Peterson

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 13:05:53 GMT

Well, I guess you are kind of right, but I can not use your suggestion.

I do need to have the value between DD and RNFDAT, because in my next Column I have to calculate:


IF(RNREST<>0,IF(DD-RNFDAT<30, RNBELO,0))

I am sorry it did not work. but thank you for trying 🙂

From IF to Set Analysis (simple)

Anonymous — Wed, 19 May 2010 13:11:00 GMT

You say you need to calculate the value between the DD and RNFDAT ...

Maybe it is better to get the value between during loadingtime in the table itself ... since you want to check on the value ...

I suggest you add in the loading something like


LOAD ...
...
RFNDAT,
today() - RFNDAT as TimeDiff
...

This way you can do something like

SUM( {< RNREST -= {0}, TimeDiff = {'<0'} >} RNBELO )

and in the following column you can use

SUM( {< RNREST -= {0}, TimeDiff = {'<30'} >) RNBELO )

In Set Analysis you give a 'preselected' value for a field ... now TimeDiff an actual field which can be set to a specific value.

Think this is the nicest way of solving this ... it also keeps the set analysis a lot easier.

Good luck,

Anita F.

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 13:13:41 GMT

Dear Kevin,

Thank you for trying to help me, but I am not sure I understand what you are trying to accomplish.


sum({< Col2 = {'<0'}, Col4-={"0"} >} Col2)

As far as I can see, this should give the answer -5, but you get -6?

Anyway, my problem is that I have a duedate(RNFDAT), and I need to calculate the difference between (Todays date) and RNFDAT, and if the answer is 0 or 30 or 60 or 90 or >90, I want the RNBELO to be calculated, but I can only get this to work with IF sentences…

From IF to Set Analysis (simple)

Wed, 19 May 2010 13:27:19 GMT

Hi Anita

looked at your example. The difference in your example is only from the number formatting? Some of your columns use INTEGER as the format and values get rounded.

The real problem however looks to be in your expression

[DD-RNFDAT]

By enclosing them in square brackets QlikView treats it as a field name (non existent so always null) and not as the result of the date difference calculation. Modify your criteria:

SUM ({<RNFDAT={'<$(=DD)'}>} RNBELO)

try above but do not inject any blanks, this seems to break it

You might also want to add a few more cases to see the proper outcome of your formulas.

Regards

Jürg

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 13:48:57 GMT

Dear Anita F,

It acctually is a good idea, to make a TimeDiff in the load, even though I have not tried it yet.

Because I want to have "Todays date" as an Input.
[Todays date] is not always "Todays date", but if it was, I am almost certain I could use your suggested solution..

I have tried to make the Timediff in a column instead, and use the number I get in my calculations, but that does not work either.

From IF to Set Analysis (simple)

anitamelbye — Wed, 19 May 2010 13:54:18 GMT

Dear Jürg,

Thank you for trying to help me, but I am not sure that your solution would help me, because it is the difference between DD and RNFDAT I am looking for. I have tried to make the calculation DD-RNFDAT as an expression, and I get values. But maybe it acts different when I use it in a SetAnalysis?

I have, as I also answered Anita F's suggestion, tried to make the DD-RNFDAT as an expression, and use Column(1) in my SetAnalysis instead of the DD-RNFDAT-expression, but it still doesn't work..

Beeing the Newbee as I am, I think I soon give in to this problem, and use IF instead.. (but then my total-calculations gets messed up)...

From IF to Set Analysis (simple)

Wed, 19 May 2010 14:06:07 GMT

Hi Anita,

I have tried to investigate my application based on your comment. i believe the o/p whic i`m getting is the correct one.

below is my data.

LOAD * INLINE [
Col1, Col2,Col3,Col4
A, 4, x,1
B, -5, y,0
C, -6, x,3
D, 7, y,4
];

And my condition is sum({< Col2 = {'<0'}, Col4-={"0"} >} Col2)

Will try to take one by one condition.

1st condition :

Onec we execute the 1st condition i.e Col2 = {'<0'} we will get the data less than zero.

o/p will be

COl2,Col4

-5, 0

-6, 3

2nd Condition:

When we execute second condition Col4-={"0"} i.e Col4 not equal to zero

o/p will be

COl2,Col4

-6, 3

This what i tried to accomplish.

Commets are appreciateable

-Peterson

From IF to Set Analysis (simple)

Anonymous — Wed, 19 May 2010 14:09:10 GMT

Anita,

Problem you encounter is that the set analyses sets the filtering of fields to the value you want ... you want a sum to be of a certain value ...
I'm not sure if the set analysis is capable of doing this.

If the value of the date is variable ... why?? To see what it does when the date is another date??

You could try something by DD and DD-30 days ...

SUM( {< RNREST -= {0}, RNFDAT = {'>$(DD)' >} RNBELO )

SUM( {< RNREST -= {0}, RNFDAT = {'>($(DD)-30)' >} RNBELO )

Don't know if it's possible ... but guess something like this could do the trick

Anita.

From IF to Set Analysis (simple)

anitamelbye — Thu, 20 May 2010 09:30:57 GMT

Dear Jürg,

This did the trick.
SUM({RNFDAT={'<$(=DD)'}>}RNBELO) - thank you.

But I can not get it to work with (DD-30), so I think I will, after all, go for Anita F's suggestion; making a TimeDiff in the load 🙂

and

Dear Kevin, yes you are right, I just did not notice the -= 🙂 sorry for that!

Anita F - Thank you for all you help!!

*newbee in love*

From IF to Set Analysis (simple)

Thu, 20 May 2010 09:54:43 GMT

Hi Anita

The thread is already long, let's go on ..

It worked in my example with

SUM({RNFDAT={'>$(=date(DD-30))'}>}RNBELO)

in detail: RNFDAT needs to be within the last 30 days, change < to > and use the date() function to get the correct result from the subtraction.

Regards

Jürg

From IF to Set Analysis (simple)

anitamelbye — Thu, 20 May 2010 12:22:26 GMT

Dear Jürg,

THANK YOU! That works just great!! 🙂 You are the BEST!

Just one small problem left. When writing (all)RNFDAT>DD-30, I also get all the invoices with RNFDAT>DD.. you see the problem?

I need something like that takes away the RNFDAT>DD. (Range between DD-30 and DD). Are you able to help with that as well?

*newbee in love*