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

    From IF to Set Analysis (simple)

    Anita Melbye

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

          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)
              Martin FAVIER

              Hi,

              I think it should be :

               

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


              Hope it helps you

              Martin

               

              • From IF to Set Analysis (simple)
                Anita Melbye

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

                  • From IF to Set Analysis (simple)
                    Anita Melbye

                    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)
                      Anita Fuchten
                      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)
                          Anita Melbye

                          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)
                              Anita Fuchten

                              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)
                                  Anita Melbye

                                  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)
                                      Anita Fuchten

                                      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)
                                          Anita Melbye

                                          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 Fuchten

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

                                        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)
                                            Anita Melbye

                                            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)
                                              Anita Melbye

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

                                      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)
                                          Anita Melbye

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

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

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