topic Re: Calculating master calendar days in QlikView

Calculating master calendar days

jworthulf — Tue, 12 Dec 2017 16:37:58 GMT

Hi Guys,

I have a master Calendar setup on my dashboard

I need to find the number of days (Monday - Sunday) currently selected on the master calendar in order to use it as part of an expression.

LOAD

WeekDay([SHIP DT]) as DOW,

[SHIP DT],

[SHIP DT] as ship_dt,

fiscal_year,

fiscal_week,

fiscal_month,

fiscal_qtr,

monthyr,

quarteryr,

weekyr,

budget_wk

Re: Calculating master calendar days

YoussefBelloum — Tue, 12 Dec 2017 16:48:15 GMT

Hi,

on the front end, you can do this:

Max([SHIP DT]) - Min ([SHIP DT])

and when you have selections made, you can add this:

Max({1} [SHIP DT]) - Min ({1} [SHIP DT])

Re: Calculating master calendar days

maxsheva — Tue, 12 Dec 2017 19:20:25 GMT

Hi John,

Probably you need this GetSelectedCount(DOW)

When you make selection in DOW field it brings you number of selected days.

Would be nice to get feedback from you.

Re: Calculating master calendar days

jworthulf — Tue, 12 Dec 2017 19:40:16 GMT

Youssef,

I relatively new to Qlikview when you say on the "front end" are you referring to putting the formula in the Master Calendar Script?

Re: Calculating master calendar days

jworthulf — Tue, 12 Dec 2017 19:45:59 GMT

Max,

Thanks but this didn't give me the total calendar days for the time period I selected.

Re: Calculating master calendar days

maxsheva — Tue, 12 Dec 2017 19:58:29 GMT

John,

Would you clarify a meaning of calendar days for your project?

Is it week days or days from 1 to 31?

Please provide print screen what are you trying to get.

Re: Calculating master calendar days

YoussefBelloum — Tue, 12 Dec 2017 20:23:37 GMT

Hi John,

Back end it is the SCRIPT, Front end is the visualisation part.

the solution i gave you, you can try it on the Front end, on "text box" object for example.

Re: Calculating master calendar days

jworthulf — Tue, 12 Dec 2017 21:02:00 GMT

Max,

Hi, to answer your question it is the number of days from 1 to 31

Re: Calculating master calendar days

johnca — Tue, 12 Dec 2017 21:13:50 GMT

John, you need to supply more than just what you've given us. Is your calendar joined to your data by ship_dt (or other)? What are your selections? Are you counting calendar days or data days? Please provide more info on the nature of your data model and what you expect to get. Perhaps some sample data? Please help up help you.

Regards,

John

Re: Calculating master calendar days

jworthulf — Wed, 13 Dec 2017 15:00:15 GMT

John,

I have two tables a master calendar and a sales table. The sales are based on ship dates Mon - Saturday. The master calendar includes all days of the week...the issue is we do not have any sales on Sunday (no ship dates) so I'm only able to count 6 days using the solution that Youssef gave me.

Now I need a solution to count the number of calendar days (Mon - Sun) for the same time period.

Thanks,

Re: Calculating master calendar days

johnca — Wed, 13 Dec 2017 16:15:15 GMT

Okay, if you are joining sales data by SalesDate to the master calendar MASTER_DATE then the solution should be simple. Create a new field in the sales data SalesDate as MASTER_DATE to join by. Then, by selecting the SalesDate and calculating the difference between the max(MASTER_DATE) and Min(MASTER_DATE) you should get the correct value. If you want to include both min and max dates add 1. What does DOW have to do with it?

Re: Calculating master calendar days

jworthulf — Thu, 14 Dec 2017 21:38:07 GMT

John,

Thanks for the help and the example much appreciated

Re: Calculating master calendar days

jworthulf — Thu, 14 Dec 2017 21:39:47 GMT

Youssef,

Thanks for the help your response was correct as well.

Re: Calculating master calendar days

jworthulf — Wed, 20 Dec 2017 17:35:21 GMT

John,

When I select week 15 in your example the output should be 7 but it shows up as 6 why is that?

Re: Calculating master calendar days

johnca — Wed, 20 Dec 2017 18:45:02 GMT

JW,

It is simply subtracting the min date (1/2/2017) from the max date (1/8/2017). Did you need days inclusively? Add 1...

(max(MASTER_DATE) - Min(MASTER_DATE)) + 1

Re: Calculating master calendar days

jworthulf — Wed, 20 Dec 2017 19:11:50 GMT

Ok that worked ....thanks again.

Re: Calculating master calendar days

johnca — Thu, 21 Dec 2017 14:42:26 GMT

A better solution might be the following set;

=count({<MASTER_DATE={"<=$(=Max(MASTER_DATE))>=$(=Min(MASTER_DATE))"}>}DOW).

If this question has been answered please close it by selecting the answer and designating it as such.

Re: Calculating master calendar days

jworthulf — Thu, 21 Dec 2017 18:45:50 GMT

John

I tried using your update but unfortunately it didn't work. I ended up using the following based on the feedback I received from you & Youssef.

COUNT({$<VENDOR=,Units=,BUYER=,ItemID=>}TOTAL MASTER_DATE+1)

Thanks again for all your help.

Happy Holidays,