topic Re: How many days does a week have? in QlikView

How many days does a week have?

datanibbler — Mon, 26 Aug 2013 15:15:59 GMT

Hi,

a very trivial question - and I already have an inkling what the answer is. I just want it confirmed - and it would be nice to know how I might change it 😉

The background is, I have a status_traffic_light that calculates the avg of the past week (currently week #34) and if it exceeds the target_value, the traffic_light is green, otherwise it is red.

Just now, the light was red though we made good figures from Mo to Fri. There was no data on Saturday.

Now I edited the formula in the underlying Excel sheet so when there is no data, the target value appears. Now the light is green, but by simply calculating the avg, I guess that a week in QlikView still has seven days - normal.

However, in some diagrams, I calculate the avg to be able to display monthly figures - and that traffic_light usually uses an avg, too.

Now, how can I change that so that a week has only six days - or would that mess up my monthly figures?

That would still not be quite correct as we don't work every Saturday, but it would be closer as we never work on Sundays.

Thanks a lot!

Best regards,

DataNibbler

Re: How many days does a week have?

rajeshvaswani77 — Mon, 26 Aug 2013 15:35:31 GMT

Hi,

You might need a SharePoint list to record holidays in a yer so that you could exclude them.

Also you might consider days that are not saturdays or sundays.

thanks,

Rajesh Vaswani

Re: How many days does a week have?

Oleg_Troyansky — Mon, 26 Aug 2013 16:55:58 GMT

Maybe you could use the function NetWorkDays() that automatically excludes weekends.

Alternatively, you could use Advanced Aggregation (AGGR) to always calculate daily averages (without dividing by the number of days). This way, if there is any data on a Saturday, it can be included, and if there isn't, it would not be included...

Re: How many days does a week have?

datanibbler — Tue, 27 Aug 2013 07:07:46 GMT

Hi,

@Rajesh,

You are right - I don't care about holidays, I just want to exclude weekends - rather, only Sundays.

@ Oleg

I cannot calculate any daily averages, there is just one discrete value per day - underlying is a manually populated Excel list. I have now set the formula (in one diagram, but it seems a good idea for all) to always return the target_value if there is no data. That way, an average would not be altered and I can just always include Saturdays.

Actually, I think that just about solves my problem - if I have two additional days (Sat and Sun) without data, the list just returns the target_value. I guess I have to adapt this so that it just returns 1, then the average should stay exactly the same, shouldn't it?

P.S.: Hmm - that is easy enough and for one of my metrics where the target_value is 1.4 - but for the other it is 37.6, so assuming 1 when there is no data would lower the average quite a lot. Maybe assuming the target_value is the safer way to go - that way, figures will be a little queer, but still close.

Re: How many days does a week have?

rajeshvaswani77 — Tue, 27 Aug 2013 08:00:58 GMT

Hi,

You might create a snapshot as well. This will accept a startdate and a end date (say today) and then generate the dates. Next for the date it will have the target value. We can have a day as well. When we create a expression then we exclude day equals saturday. Not so sure if this makes good sense though. Just a thought.

thanks,

Rajesh Vaswani

Re: How many days does a week have?

datanibbler — Wed, 28 Aug 2013 10:19:52 GMT

Hi,

it seems that just now everything is all right. In most apps, I have some textboxes with historical values, usually the past week and the past month - so I can see immediately whether the status is right or wrong.

However, I am still not sure about this:

I can well live with Saturday being included in the avg calculation ((Mo+...+Sat)/6). There might or might not be data for Sat, but that is something we have to cope with.

What about Sunday, however? In many Excel lists, there is a field for every Sunday and, of course, a 0 for value. Some of them I cannot edit. If that were included in the avg calculation, it would mess it up a lot.

I use a set_expression to calculate the avg for the past week and compare that to the target value.

Is there a way I can make this calculation take into account only Mo-Sat?

Thanks a lot!

Best regards,

DataNibbler

Re: How many days does a week have?

Oleg_Troyansky — Wed, 28 Aug 2013 16:01:43 GMT

The easiest way to limit your calculation to weekdays only is to create a new calendar field, Weekday_Flag and to assign 1 for all weekdays and 0 for all weekend days (you can do it in the script, where you generate your master calendar). Then, in the expression, you simply add one more Set Analysis modifier:

Weekday_Flag={1}

Re: How many days does a week have?

datanibbler — Thu, 29 Aug 2013 06:43:51 GMT

Hi Oleg,

that sounds very good. Actually, I think there is already such a weekday_flag in the calendar.

So I can just say (in the set_expression) like

>> Week=(Week(TODAY())-1), weekday_flag={1} <<

can I not?

Wonderful!

I haven't yet had a case where the status_light was definitely wrong, but I guess that would be the safer way to go.

I can also create a second weekday_flag so that one would encompass only Mo-Fri and one would encompass Mo-Sat.

Re: How many days does a week have?

Oleg_Troyansky — Thu, 29 Aug 2013 16:26:36 GMT

Yep, exactly!