Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anitamelbye
Creator
Creator

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*

25 Replies
Anonymous
Not applicable

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.

anitamelbye
Creator
Creator
Author

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…

Not applicable

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

anitamelbye
Creator
Creator
Author

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

anitamelbye
Creator
Creator
Author

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

Not applicable

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

Anonymous
Not applicable

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.

anitamelbye
Creator
Creator
Author

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*

Not applicable

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

anitamelbye
Creator
Creator
Author

Dear Jürg,

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

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*