Friday, May 24, 2013

Custom dimension values using "ValueList" or "ValueLoop"

Some times we have to generate user defined dimension values, for example If we want to see list of expressions for Previous and Current year, where Previous and Current year or not direct values from a field.

A straight table that look like below.

                             CurrentYear     PreviousYear    Forecast  Variance

In cases like above where we have to derive custom dimension values, ValueList will be used.

Declaring the dimension:


Defining expressions:

Logic is : Explicitly checking the value in valuelist and defining the expression for that dimension value

= If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'CurrentYear', sum({$<  [Year]={$(vCurrentYear)}>} Sales),

  If(valuelist('CurrentYear','PreviousYear','FCST','Variance') = 'PreviousYear',(sum([Year]={$(vPreviousYear)} >} Sales)),

  If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='FCST',(sum({$<  [Year]={$(vPreviousYear)} >} Sales))/$(vFCST_Factor)

 If(valuelist('CurrentYear','PreviousYear','FCST','Variance') ='Variance',(sum({$< [Year]={$(vCurrentYear)} >} Sales))/$(vFCST_Factor) )/(sum({$<[Year]={$(vPreviousYear)} >} Sales))-1 ))


Thursday, May 23, 2013

Functions which won't support Alternative states and its work around

Functions which basically operate on current selection state like
GetFieldSelections, GetPossibleCount will not take parameter as Alternative states.

some thing like GetFieldSelections(<State1> Field1) is not possible in Qlikview 11.

Instead we can use general functions like Maxstring, Concat, Count functions.

For example if we want to know selection made in a field which has an alternative state and have 'Allways one selection'.
In this case for accessing the selection made in that field we can use.

maxstring({[State1]} [Field1]).

If allways one selections is disabled and  for getting all selections we can use.

=concat({[State1]} distinct [Field1],'|')

For getting Selection count we can use.

=count({[State1]} distinct [Field1])

Hope it helps :)