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

[#1]

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.

[bgerchikov]

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.

[#3]

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

[#4]

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]= >}

[bgerchikov · Accepted solution]

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)

[#6]

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