topic date calculation based on number of days in QlikView

date calculation based on number of days

karensmith — Thu, 30 Jul 2009 21:23:58 GMT

To accurately display Sales vs Budget values. I have to annualize budget numbers.Sales is loaded daily by invoice date, but Budget is reported monthly.

I will need to calculate Budget/days in month * current day -1

Example:

Budget amount for July is 28075

28075/31*29 = 26,263.

Can someone please help me with the correct syntax to calculate this formula. I still have trouble using all the date fucntions in QV.

Do I need to use a variable? Currently, we do not have any variable is our application. How can I dynamically handle the different amount of days in a month and calculated based on previous day.

I currently use YTD flags based on our fiscal/gregorian calendar. I would need to result with chart planvalue calculation below.

Can someone please advise how should handle this in the script and/or chart.

Sum

(SALESVALUE * FY_YTD_Flag)/Sum(PLANVALUE * FY_YTD_Flag)

Planvalue= budget value.

)

date calculation based on number of days

karensmith — Thu, 30 Jul 2009 21:50:52 GMT

I forgot to add that we also calculate LY YTD.

date calculation based on number of days

karensmith — Thu, 30 Jul 2009 22:42:55 GMT

I was able to use this formula when 30 days is hard coded, but I actually need to calculate the number of days in the month:

Sum(

(PLANVALUE* FY_YTD_Flag)/30)* (date(today())-date(monthstart(today())))

how can I handle the days in a month calculation?

date calculation based on number of days

karensmith — Fri, 31 Jul 2009 00:00:55 GMT

Okay... I'm actually beginning to answer my own questions, but continue to run into road blocks.

Sum

(PLANVALUE* FY_YTD_Flag)/((ROUND(monthend(TODAY())-monthstart(TODAY())))) * (date(today())-date(monthstart(today())))

This works fine when looking at YTD. But when user wants to compare LY YTD as of the same date. This formula is not correct because the day calculations are going to be based on the current day and current month.

Can someone please help me out.

I need to show object that shows Sales vs Budget YTD and Sales Vs Budget LY YTD. Budget is calculated on days in month * days that have passed.

Is my logic incorrect? does it makes sense to make this type of comparison? Or should LY YTD be the complete month values?

date calculation based on number of days

Oleg_Troyansky — Fri, 31 Jul 2009 01:43:45 GMT

Karen,

you are on the right path, only you could simplify your expressions a lot if you could pre-calculate some flags in your script.There are many ways of approaching this problem, here is one:

Pre-calculate the field "YTD Monthly Multiplier" and link it to the field Year/Month in your calendar:

- for all YTD Year/Month values, except for the current month, the Multiplier should be 1.

- for the CUrrent Month (including PY Current Month), the Multiplier = <number of days that passed till today> / <number of days in the current month>

- for all the months beyond today, the Multiplier should be equal to 0.

After loading a field into your Claendar table, your expression for YTD Budget will be very simple:

YTDBudget = sum(Budget*YTD_Flag*YTD_Multiplier)

PY_YTD_Budget = sum(Budget*PY_YTD_Flag*YTD_Multiplier)

good luck!

Oleg

date calculation based on number of days

karensmith — Fri, 31 Jul 2009 02:31:30 GMT

Thanks Oleg,

This is my calendar load statement. I started using YTD flags for both our FY year and Gregorian Year. Calculations were working great, until it dawned on me that when comparing back to previous year would not truly current year's YTD time period.

Are you saying to completely scrap my previous calculations-use flags and multipliers and then your new code fir YTD budget ad PY_YTD_Budget on the object?

Year(DATE([Fiscal Year],'YYYY')) as FYYear,

fiscalQuarter

,CalendarQuarter

Year2Date(CalendarDate,0,4) * -1 as FY_YTD_Flag

,Year2Date(CalendarDate, -1,4) * -1 as FY_LY_YTD_Flag

,Year2Date(CalendarDate) * -1 as CAL_YTD_Flag

,Year2Date(CalendarDate,-1) * -1 as CAL_LY_YTD_Flag

,Year(DATE([Calendar Year],'YYYY')) as CalYear

,date((CalendarDate),'MM/DD/YY') as INVOICE_DATE

,date((CalendarDate),'MM/DD/YY') as INVDT

,date(monthstart(num(CalendarMonthYear)),'MMYYYY') as MONTHYEAR

,num(Month) as FYMonth

from excel spreadsheet

"YTD Monthly Multiplier"- will this be a seperate table when you say "link it to field Year/Month"???? Or do I need to preceding load?? Just to confirm I am onl creating one field, but adding two flags? Can I use the flags that I already have and make YTD modifiier from MonthYear field.

Does this mean I create a new field in calendar load - date(monthstart(num(CalendarMonthYear)),'MMYYYY') * 1 as YTD Monthly Mulitplier

I will go ahead an try to attack this. What you have suggested seems to make alot of sense. When calculating number of days passed/number of days in the current month? Should I use my original formula?

Your help is greatly appreciated.

date calculation based on number of days

karensmith — Fri, 31 Jul 2009 03:31:22 GMT

Updated load script with new flag calculations, but I'm still confused on how to "Pre-calculate the field "YTD Monthly Multiplier" and link it to the field Year/Month in your calendar"

LOAD

DISTINCT

Year

(DATE([Fiscal Year],'YYYY')) as FYYear

FiscalQuarter

,CalendarQuarter

Year2Date(CalendarDate,0,4) * 1 as FY_YTD_Flag

Year2Date

(CalendarDate, -1,4) * 1 as FY_LY_YTD_Flag

Year2Date

(month((CalendarDate,0,4))) *1 as FY_MTD_Flag

Year2Date

(month((CalendarDate,0,4))) *1 as FY_LY_MTD_Flag

(num(FYMonth) > num(month(today()),num(month(today())))*0) as future_mth_flag

(num(FYMonth) < num(month(today())),num(month(today()))*1 )as FlagMonth

(num(FYMonth) = num(month(today())),num(FYMonth)*(date(today())-date(monthstart(today()))/ ,((ROUND(monthend(TODAY())-monthstart(TODAY())))),0)) as CurrentMonghFlag

Year2Date

(CalendarDate) * -1 as CAL_YTD_Flag

Year2Date

(CalendarDate,-1) * -1 as CAL_LY_YTD_Flag

Year

(DATE([Calendar Year],'YYYY')) as CalYear

date

((CalendarDate),'MM/DD/YY') as INVOICE_DATE

date

((CalendarDate),'MM/DD/YY') as INVDT

,date(monthstart(num(CalendarMonthYear)),'MMYYYY') as MONTHYEAR,

num

(Month) as

FYMonth

FROM

.. \EXCEL\Fiscal_Calendar_v2.xls (biff, embedded labels, table is Sheet1$)

;

date calculation based on number of days

Oleg_Troyansky — Fri, 31 Jul 2009 04:58:43 GMT

Karen,

You might have to load your Calendar, then perform some calculations, and then reload the calendar again with the new flag.

Since you have both Fiscal Periods and Calendar Months, you need to decide what to use for budget pro-rated calculation. It's a bit easier to do with Calendar Months, but if your Budget is stored based on Fiscal Calendar, you have to stick to Fiscal then.

The way I'd approach it is this. Before your Load statement, you need to calculated a few variables:

1. Create a variable for "Current FP".

2. Create a variable and calculate number of days in your Current Fiscal Period.

3. Create another variable and calculated number of days that passed since the beginning of the Fiscal Period.

4. Create the final Multiplier variable by dividing the numbers of days above. Let's say you called it "vMult".

Now, in your load, you can do something like this:


if( FY_LY_YTD_Flag = 0 and FY_YTD_Flag = 0, 0 , // if not YTD and not Prior YTD, then 0
 if(FY_LY_MTD_Flag = 1 or FY_MTD_Flag = 1, $(vMult) // IF MTD or Prior Year MTD - use calculated multiplier
 1)) // For all other YTD or PY YTD dates - use 1 (full budget without pro-rating)

hope it makes sense...

Oleg

date calculation based on number of days

karensmith — Fri, 31 Jul 2009 09:52:14 GMT

Thanks again Oleg,

I've tried, did not get expected results. Variables created correctly, but the MTD and CY and PY numbers are monthly grand totals, rather than using ratio. Don't know what I'm doing wrong.

I have attached script file. Maybe you can take a look. Thanks......