Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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*
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 )
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.
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…
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
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
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)...
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
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.
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*
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
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*