4 Replies Latest reply: Mar 6, 2018 10:36 AM by Juraj Misina RSS

    Proper creation of IF function

    Marios Mylonas

      Dear all,

       

      I have an issue I would like to solve, but I have never used the IF function before and therefore I do not get results. i guess that there must be some syntax errors and that is the reason I am not getting any results. I am copying below my code. But let me give you an inside on what I want to do so that you can understand. (I want this data to be viewed on a table)

       

      Basically, the reason I want the if function, is due to the fact that some months have different number of days than others. e.g. February with March etc. I run 4 houses and I would like to calculate the % of my occupancy. So If I have offered in all 4 houses accommodation for a total duration in all 4 houses of 64 days in February, I would like that to be divided by 4*28. I would like the same to happen for March, but instead of 4*28 to be 4*31.

       

      So what I tried below to do was, saying if the months are e.g. Jan, Mar, May, Jul. Aug, Oct, Dec, then I would like the sum of the occupancy %. the occupancy % should be calculated as duration/total days per month. Duration is [end.autoCalendar.Date]-[start.autoCalendar.Date]) while the totla days per month should be 4*Number of days in each month

       

      Thank you in advance

       

       

       

       

       

      if({$<[start.autoCalendar.Month]={JAN,MAR,MAY,JUL,AUG,OCT,DEC}>}) then

      Sum({$<start.autoCalendar.Year={$(=Year(today()))}>}[ [end.autoCalendar.Date]-[start.autoCalendar.Date])/(4*31)]) ;

      end if

       

      if ({$<[start.autoCalendar.Month]={APR,JUN,SEP,NOV}>}) then

      Sum({$<start.autoCalendar.Year={$(=Year(today()))}>}[ [end.autoCalendar.Date]-[start.autoCalendar.Date])/(4*30)]);

      end if

       

      if ({$<[start.autoCalendar.Month]={FEB}>}) then

      Sum({$<start.autoCalendar.Year={$(=Year(today()))}>}[ [end.autoCalendar.Date]-[start.autoCalendar.Date])/(4*28)]);

      end if

        • Re: Proper creation of IF function
          Juraj Misina

          Hi Marios,

          can you provide some sample data so that we better understand your situation? It seems like you're trying to use set analysis in your If statement, which will not work, but in order to help you find the right solution it would be good to see a few lines of data or perhaps a sample app.

          Juraj

            • Re: Proper creation of IF function
              Marios Mylonas

              Dear Juraj,

              Thank you for your reply. I am attaching below a screenshot of the csv file that I am uploading in the qlik as a dataset. I hope that this was what you were asking. Please also note that I have deleted the information that was filled with names for obvious reasons

              Capture.PNG

              • Re: Proper creation of IF function
                Marios Mylonas

                I have thought of a different way to do it, without the use of if. However, I get no results and I guess it could be due to a syntax error. Basically, i thought of deducting the last date of the month from the first day of the month and adding 1 day back. If that is the denominator, then I will be having what I want

                 

                Sum({$<start.autoCalendar.Year={$(=Year(today()))}>} ([end.autoCalendar.Date]-[start.autoCalendar.Date]/(monthend([end.autoCalendar.Date])-monthstart([end.autoCalendar.Date])+1)

                  • Re: Proper creation of IF function
                    Juraj Misina

                    Hi Marios,

                     

                    this looks much better. If this is exact copy of your expression, there indeed were two typos. This should be OK syntax-wise:

                    Sum({$<start.autoCalendar.Year={$(=Year(today()))}>} ([end.autoCalendar.Date]-[start.autoCalendar.Date])/(monthend([end.autoCalendar.Date])-monthstart([end.autoCalendar.Date])+1))