Resource problem with a diagram - formulas too complex - Plz help

[datanibbler]

Hi,

I have a diagram that is mainly for management to see and that displays

- a list of all the KPIs in the app (in a table)

- a YTD-value for each and

- a status_light that is dependent on the YTD_value.

The problem is, the formula for calculating the YTD_value of a presence quota, such as I have it, is quite complex. I have that formula three times for the YTD-value (three plants) and I have three status_lights which basically repeat the formula and compare it to a fixed target_value.

Another complicating factor is that all this is just ONE diagram, but every KPI (every line) needs an individual formula for the YTD_value - so I have a large nested IF-construction with a different formula specified for every line.

Now, since I built in the YTD_values for the three presence quotas yesterday, the diagram will kill itself loading - the script is relatively quick and without problems.

The formula I have for calculating a YTD_value for this is currently

({1}DISTINCT PN_H3&Jahrestag) / COUNT({1<%Datum = {">=01.01.2013"}, %Datum = {"$(= '<' & v_today_dt)" }>} Werktag)
     - COUNT({1<Kennz_krank_H3={'X'}>}DISTINCT PN_H3&Jahrestag) / COUNT({1<%Datum = {">=01.01.2013"}, %Datum = {"$(= '<' & v_today_dt)" }, Werktag={1}>} Werktag))
     /
     (COUNT({1}DISTINCT PN_H3&Jahrestag) / COUNT({1<%Datum = {">=01.01.2013"}, %Datum = {"$(= '<' & v_today_dt)" }>} Werktag)))

(I count the people*days YTD and divide it by the nr. of days YTD,

then I subtract (the nr. of people*days who have been out sick, divided by the nr. of days) and finally

I divide that by (the first line again).

All that three - nay, six times - is obviously too much for my machine.

How can I make this lighter?

Thanks a lot!

Best regards,

DataNibbler

P.S.: It seems that the COUNT also returns the wrong nr. of days - 365 instead of 261. I'm confused because I tested it beforehand, but now it seems I'll have to replace every instance of COUNT by SUM.
I will try putting that calculation (the days past YTD) in a variable that I can use in those formulas instead of typing it out every time.

P.P.S.: Okay - alwaysch schlowly as the Latins say 😉 I realized I had some consistency problems between my apps - the variables in part had different names, causing the formula to fail. I will solve that now for good by creating a few qvs files for INCLUDEs, in one of which I'll have my formula for days_past_YTD. Then I can use that in the formulas for that big diagram and hopefully that will solve my problem.

P.P.P.S.: Oh Sh... I cannot use set_expressions in the script, so I cannot put the formulas I would use to calculate some variables in a qvs file...

[datanibbler]

Hi,

I have a very specific problem now:

- I have a master_calendar that goes back two years and until Dec 31 of this year.

- In the formula for my variable (the nr. of working_days YTD), I have actually specified Jan 1 of this year as start_date

<=> Still, the variable now tells me there have been about 500 working_days.

<=> When I edit the qvs file so that the calendar has only this year, the figure is correct.

My current code for the variable (not in the qvs file, but in a template I have created) is:

= SUM({1<%Datum = {">=01.01.2013"}, %Datum = {"$(= '<' & DATE(v_today_num))" }>} Werktag)

(where "Werktag" is a binary field, returning 1 for Mon-Fri and 0 for Sat-Sun and "%Datum" is my date_field, used to link most diagrams to the master_calendar.

Can anyone help me here, please?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I have replaced that hard-coded date with a formula now to make the thing flexible so that I won't spend Jan 2 of next year repairing everything - it works, but the problem remains the same.

[datanibbler]

Please - this is really a problem.

I have "fixed" it for now by editing the qvs file so that the calender is for this year only - but I know that for some diagram(s) in the app I am working on (it was originally created by a colleague) a longer-range calendar is necessary, so that is not a long-run solution.

My code for the variable is now

SUM({1<%Datum = {"$(= '>=' & makedate(year(today()), 1, 1))"}, %Datum = {"$(= '<' & DATE(v_today_num))" }>} Werktag)

("Werktag" is my binary field, %Datum is generally the date_field, "v_today_num" is the numerical value of (TODAY)).

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

four hours, one qvs file, several variables, two posts here and two large coffee later my original problem is solved.

That problem with that calendar, however, remains.

Maybe i should open a new thread for that?

[tresesco]

Hi Hofmann,

Can realize your despair. And it's true that you are not new to this community, still, it would be my suggestion to go through these two threads:

How to get answer to your post  and Preparing example for upload

Thanks.

[datanibbler]

That citation by Robert Half is great 😉

I will begin by trying once again - as you've realized, I have been in deep waters today, there might have been something wrong which is ok now, so it might just work now. If now, I will attach some txt files with my master_calendar_qvs and the code for my variable and for the final expression.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

No, unfortunately the problem remains.

So now I will attach some things:

- the qvs file I have created with my calendar and some variables

- a txt file with the formula I use for creating my variable.

The formula is not the problem - I have solved my original problem: In that one app where the formula was calculated six times, resulting in the laptop breaking down, the use of that variable helped.

The problem, which I cannot spot the reason for, is this:

- When I have the calendar for the current year only (not the way it is now), the figures are correct - 188 working_days YTD, not taking into account public holidays.

<=> When I enlarge the calendar to three years (the way it is now), I get three times the nr. of working_days...

Is that precise enough to pinpoint the reason for my problem?

Thanks a lot!

Best regards,

DataNibbler