topic Re: Set Analysis in Chart Expressions. in QlikView

Set Analysis in Chart Expressions.

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis in Chart Expressions.

trdandamudi — Thu, 07 Jul 2016 13:41:23 GMT

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...

Re: Set Analysis in Chart Expressions.

Thu, 07 Jul 2016 14:16:52 GMT

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

Re: Set Analysis in Chart Expressions.

Anonymous — Thu, 07 Jul 2016 14:37:57 GMT

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.

Re: Set Analysis in Chart Expressions.

boorgura — Thu, 07 Jul 2016 14:57:50 GMT

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.

Re: Set Analysis in Chart Expressions.

Thu, 07 Jul 2016 15:11:25 GMT

_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