topic Re: Current Month-End Set Analysis Formula in QlikView

Current Month-End Set Analysis Formula

triekong — Sat, 15 Sep 2018 00:13:17 GMT

hi All,

I have 2 metrics in my dataset: Actuals and Forecast. Actuals show what has already happened and Forecast is what we predict we will spend in the future.

My fiscal year starts in September(Month 1, 1st Quarter) and Ends in August(Month 12, 4th Quarter). that is in the Master Calendar Script.

This is a month-end dataset, so we are currently in September, the dashboard should show Actual data for August. Since September hasn't ended yet, Actuals for September will be $0

But Forecast data can be shown for any future months.

I need to calculate the current month-end's actuals, and the current quarter's actuals using Set Analysis Expression.

The current month-end would be the actuals for the month of August.

The current quarter would be the actuals for the 4th Quarter Q4.

But I cannot just do vMax_Month-1, because what if I am in the 1st month of the fiscal year, then my previous month will be based on a value of '0'.

How do i achieve this?

I have attached a sample qvw file and dataset.

FYear= fiscal year

FMonth= fiscal month

fMonth=fiscal month in numeric

FQuarter= fiscal quarter

FQuarter_Num= fiscal quarter in numeric

Re: Current Month-End Set Analysis Formula

captain89 — Sat, 15 Sep 2018 11:37:16 GMT

Hi,

try this:

In the script add these two fields:

(Year + If(Month>=$(vFM), 1, 0))*100+ (Mod(Month-$(vFM), 12)+1)*1 as fYearMonth,

if((Mod(Month-$(vFM), 12)+1)*1=1,

(Year + If(Month>=$(vFM), 1, 0))*100+ (Mod(Month-$(vFM), 12)+1)*1-89,

(Year + If(Month>=$(vFM), 1, 0))*100+ (Mod(Month-$(vFM), 12)+1)*1-1) as fYearMonth_1,

set a variable like this:

v_PreMonth=max(fYearMonth_1)

and then your formula:

=' Current Month-End'&chr(39)&'s Actuals '& Sum({<FQuarter=,FMonth=, [Date]= ,fYearMonth={$(v_PreMonth)}>}[Actuals])

Re: Current Month-End Set Analysis Formula

captain89 — Mon, 17 Sep 2018 12:54:05 GMT

Did you solve the problem?

Can I have my mark now?