25 Replies Latest reply: May 21, 2010 7:34 AM by emmis

# From IF to Set Analysis (simple)

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)

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)

Hi,

I think it should be :

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

Hope it helps you

Martin

• ###### From IF to Set Analysis (simple)

No, it still doesn't work.. :-)

• ###### From IF to Set Analysis (simple)

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))`

• ###### From IF to Set Analysis (simple)
`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)

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)

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)

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)

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 ...

`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)

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.

A

• ###### From IF to Set Analysis (simple)

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)

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)

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)

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)

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)

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*

• ###### From IF to Set Analysis (simple)

This actually works..

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

*newbee feelin' clever*

• ###### From IF to Set Analysis (simple)

Or .. not.. :-(

Jürg..? :-)

Edit: I just saw your suggestion.. trying now.. ;:-)

• ###### From IF to Set Analysis (simple)

You wrote: SUM({<RNFDAT={'>\$(=date(DD-30)) <\$(=DD)'}>}RNBELO), and it did not work, but when I swiched places to:

SUM({< RNFDAT={'<=\$(=Date(DD))>\$(=Date(DD-30))'}>} RNBELO ) it worked :-) I dont know why, and I really dont care..

Did you hear? it works... YEAH!

THANK YOU Juerg

• ###### From IF to Set Analysis (simple)

saw it, thanks, so I hope still in love but not a newbee any more, keep qliking!

• ###### From IF to Set Analysis (simple)

Hi Anita

After calling me the BEST I had to come up with something.

Needed to create some data first, can you try

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

Maybe change > to >= and/or < to <= as of your business rules

Regards

Juerg

• ###### From IF to Set Analysis (simple)

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)

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)

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)

Hi Anita,

You can use somethig like this as dimension

if(num(DueDate)-num(today())<90,Class(num(DueDate)-num(today()),30,'arrears'),'arrears>=90')