Solved: From IF to Set Analysis (simple) - Qlik Community

anitamelbye · ‎2010-05-19

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*

Anonymous · ‎2010-05-19

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.

View solution in original post

Report Inappropriate Content · ‎2010-05-19

Hi Anita,

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

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

-Peterson

martin59 · ‎2010-05-19

Hi,

I think it should be :

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

Hope it helps you

Martin

anitamelbye · ‎2010-05-19

No, it still doesn't work.. 🙂

anitamelbye · ‎2010-05-19

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

Anonymous · ‎2010-05-19

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.

anitamelbye · ‎2010-05-19

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 🙂

Anonymous · ‎2010-05-19

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.

Report Inappropriate Content · ‎2010-05-19

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

anitamelbye · ‎2010-05-19

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 🙂