Set Analysis in Chart Expressions.

[#1]

Hi

Set Analysis in Chart Expressions.

As a newcomer to QV I am struggling on the following and would appreciate some help :

Analysing the General Ledger, I am trying to select forecast data (_FCast1) if the selection in _Per1 (months 1 up to 12)
is greater than the current Actual period (_CurrentPer) where _Per1 is a selection field that will span periods either side of the_CurrentPer

All I keep getting is 'Actual' data
I am using :

NUM((
sum({$<
GL.Year={$(=(maxstring(_Yr1)))},
GL.Period={$(=(getfieldselections(_Per1,',',12)))},
GL.ForecastCategory={$(=if(getcurrentfield (_Per1) > maxstring(_CurrentPer), maxstring(_Fcast1), 'Actual' )) }
>}
[GL.Value]

Thanks

[trdandamudi]

I think you are missing single quotes.. Give a try on the below expression...

NUM((

sum({$<

GL.Year={'$(=(maxstring(_Yr1)))'},

GL.Period={'$(=(getfieldselections(_Per1,',',12)))'},

GL.ForecastCategory={'$(=if(getcurrentfield)' > maxstring(_CurrentPer), maxstring(_Fcast1), 'Actual' ) }

>}

[GL.Value]

Is this doesn't work can you please post a small sample...

[#3]

Tried this - it gets rid of actual data also !

Here is a (Very) small sample of data, thanks

GL.ForecastCategory	GL.TransDescription	GL.Value	GL.Year	GL.Period	BusinessUnit
Actual	GROSS SALARIES	22364.98	2016	1	501
Actual	GROSS SALARIES	25834.97	2016	2	501
Actual	GROSS SALARIES	33257.33	2016	3	501
Actual	GROSS SALARIES	34108.93	2016	4	501
Actual	GROSS SALARIES	34400.6	2016	5	501
Actual	GROSS SALARIES	34628.03	2016	6	501
M07F16	GROSS SALARIES	22364.98	2016	1	501
M07F16	GROSS SALARIES	25834.97	2016	2	501
M07F16	GROSS SALARIES	33257.33	2016	3	501
M07F16	GROSS SALARIES	34108.93	2016	4	501
M07F16	GROSS SALARIES	34400.6	2016	5	501
M07F16	GROSS SALARIES	34628.03	2016	6	501
M07F16	GROSS SALARIES	35000	2016	7	501
M07F16	GROSS SALARIES	35000	2016	8	501
M07F16	GROSS SALARIES	35000	2016	9	501
M07F16	GROSS SALARIES	35000	2016	10	501
M07F16	GROSS SALARIES	35000	2016	11	501
M07F16	GROSS SALARIES	35000	2016	12	501

[Anonymous]

Apart from single quotes. For num function you should give the number format parameters. See example below

num($(=%KPIPct),'#,##0.0#%')

Share the sample file we might be able to help you and let us know what format the data needs to be in.

[boorgura]

NUM((

sum({$<

GL.Year={$(=(maxstring(_Yr1)))},

GL.Period={$(=(getfieldselections(_Per1,',',12)))},

GL.ForecastCategory={$(=if(getcurrentfield (_Per1) > maxstring(_CurrentPer), maxstring(_Fcast1), 'Actual' )) }

>}

[GL.Value]

GetCurrentField is to retrieve the current field within a GROUP. But you mentioned _Per1 is a FIELD.

[#6]

_Per1,           _Yr1           and               _Fcast1  

are all List boxes from data islands created from all possible data in the GL.

I am using data islands as there are many more tables to be joined to the GL data

These other data sets have currently been dropped from the load script