Proper creation of IF function

[omodoshouses]

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

[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

[omodoshouses]

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

[omodoshouses]

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)

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