topic Month to date Variable not working in QlikView

Set and Variable syntax help: Inherited syntax, but having trouble w maintenance

Tue, 14 Feb 2012 02:28:47 GMT

I am trying to get my pivot table to show Month To Date Forecast (MTD) in the Fcst MTD Column and will need to create a Fcst YTD Column too.

My company operates on the fiscal calendar October to September (for example October 2009 to September 2010)

I have created a Fcst MTD expression but it Defaults to September instead of February (since we are in the MTD February)

However, if i manually make the selection Feb, then the Fcst MTD expression corrects itself and displays the correct Fcst amounts.

My attached QVW, includes a Customer Table, Fcst Table and Fiscal Calender Table.

It appears that there are some Syntax corrections that need to be made to improve the definitions for some of my variables. However i have been unsuccesful in making the correct Syntax adjustments to get my MTD & YTD to work properly.

The Variable Syntax and Definitions were inherited and I am learning how to mainteance and have been fine until this point.

Any help would be greatly appreciated as this is a project i am working on at Work.

Month to date Variable not working

bgerchikov — Tue, 14 Feb 2012 05:37:21 GMT

Hi Joseph,

Please review values for your variables:

sCMTDFcst = {$<[Calendar Date]={">=num(only({<[Fiscal YYYYMM]={$(#vCurrMthFcst)}>} [Fiscal Month Start Date])) <=$(#vCMEndFcst)"}

vCMEndFcst = 8/26/2012

vCMStartFcst = num(only({<[Fiscal YYYYMM]={$(#vCurrMthFcst)}>} [Fiscal Month Start Date])) = 8/26/2012

vCurrMthFcst =only({<[Calendar Date]={"$(vLastDayFcst)"}>}[Fiscal YYYYMM]) = 201212

vLastDayFcst =if(vOpenFlagFcst=0,date(max([Calendar Date]),'MM-DD-YYYY'),vLastFcstDate) = 08-26-2012

vLastFcstDate =date(max({<RecType={"Fcst"}>} [Calendar Date]),'MM-DD-YYYY') = 08-26-2012

Your start date has the same value as the end date (Which is September). If you expand your expression and subtract 12 months from the start date, the data will be displayed properly.

SUM({$<[Calendar Date]={">= $(#vCMStartFcst)-12 <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >} Fcst)

So, you need to change definition of the start date variable - vCMStartFcst.

I hope it will help you.

Re: Month to date Variable not working

Tue, 14 Feb 2012 16:39:47 GMT

Boris Gerchikov,

thank you for your input.

I changed my expression in the pivot table to the below and It didnt work.

SUM({$<[Calendar Date]={">= $(#vCMStartFcst)-12 <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >} Fcst)

Next, I attempted to change my vCMStartFcst variable definition to the below

=num(only({<[Fiscal YYYYMM]={$(#vCurrMthFcst)-12}>} [Fiscal Month Start Date]))

I reloaded and it didnt work.

Attached an updated QVW

Please help with Syntax, as i am still learning how to modify and i inhearited the original Syntax and have to learn how to mainantce it

Re: Month to date Variable not working

Wed, 15 Feb 2012 14:06:35 GMT

Below are the definitions for my Set and Period identification, when i attempted to make the syntax adjustments suggested by Boris, i dont think i made them correctly since the problem has not been corrected

vLastFcstDate	=date(max({<RecType={"Fcst"}>} [Calendar Date]),'MM-DD-YYYY')
vOpenMthFcst	=only({1 <[Calendar Date]={"$(vLastFcstDate)"}>} [Fiscal YYYYMM])
vOpenFlagFcst	=if(max([Fiscal YYYYMM])>=vOpenMthFcst,1,0)
vLastDayFcst	=if(vOpenFlagFcst=0,date(max([Calendar Date]),'MM-DD-YYYY'),vFcstDate)
vCurrYrFcst	=only({<[Calendar Date]={"$(vLastDayFcst)"}>}[Fiscal Year])
vCurrMthFcst	=only({<[Calendar Date]={"$(vLastDayFcst)"}>}[Fiscal YYYYMM])
vCurrMthNmFcst	=only({<[Calendar Date]={"$(vLastDayFcst)"}>}[Fiscal Month Name])
vFDFMFcst	=only({<[Calendar Date]={"$(vLastDayFcst)"}>}[Fiscal Day in Fiscal Month])
vCMStartFcst	=num(only({<[Fiscal YYYYMM]={$(#vCurrMthFcst)}>} [Fiscal Month Start Date]))
vCMEndFcst	=vCYEndFcst
vCYStartFcst	=num(only({<[Fiscal YYYYMM]={$(#vCurrMthFcst)}>} [Fiscal Year Start Date]))
vCYEndFcst	=num(vLastDayFcst)
vPrevYrFcst	=vCurrYrFcst-1
vSMPYStartFcst	=num(only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)}>} [Fiscal Month Start Date]))
vSMPYEndFcst	=vPYEndFcst
vPYStartFcst	=num(only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)}>} [Fiscal Year Start Date]))
vPYEndFcst	=num(if(vOpenFlagFcst=1,only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)},[Fiscal Day in Fiscal Month]={$(#=rangemin(only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)}>} [Fiscal Month End Date]) - only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)}>} [Fiscal Month Start Date]) + 1,vFDFMFcst))}>} [Calendar Date]), only({1 <[Fiscal YYYYMM]={$(#=vCurrMthFcst-100)}>} [Fiscal Month End Date])))
vCMStartDate	=only({<[Calendar Date]={"$(vLastDayFcst)"}>} [Fiscal Month Start Date])

sCMTDFcst	{$<[Calendar Date]={">= $(#vCMStartFcst)-12 <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >}
sCYTDFcst	{$<[Calendar Date]={">=$(#vCYStartFcst) <=$(#vCYEndFcst)"} ,[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]=>}
sCYearFcst	{$<[Fiscal Year]={$(#vCurrYrFcst)},[Fiscal Quarter]=,[Fiscal Month Name]= >}

Re: Month to date Variable not working

bgerchikov — Wed, 15 Feb 2012 14:49:49 GMT

Joseph,

If I understand correctly you need YTD forecast, right? It means if you select Feb in your calendar then the forecast data October through February will be shown.

Therefore your start date should be vCYStartFcst.

So, your expanded expression will be as follows:

sum({$<[Calendar Date]={">=$(#vCYStartFcst) <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >} Fcst)

Re: Set and Variable syntax help: Inherited syntax, but having trouble w maintenance

Wed, 15 Feb 2012 15:12:46 GMT

Boris,

Yes, that is correct, YTD would be Oct - Feb.

This works for YTD When a selection is Made .

How can I modify so they are calculated when a SELECTION is NOT MADE TOO? That way YTD and MTD will be displayed with or without a selection.

YTD

sum({$<[Calendar Date]={">=$(#vCYStartFcst) <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >} Fcst)

MTD

sum({$<[Calendar Date]={">=$(#vCMStartFcst) <=$(#vCMEndFcst)"},[Fiscal Year]=,[Fiscal Quarter]=,[Fiscal Month Name]= >} Fcst)

Thank you for all of your help