set analysis expression in Pivot dimension

Thu, 23 May 2013 09:08:33 GMT

Good people,

Question: Is it possible to configure calculated dimension to get the current period?

say user select period 2013-5,

I would like to show the Current period LY period in a pivot:

Company

Actual

2013-5

Actual

2012-5

Revenue

Gross profit

Net income

Revenue

Gross profit

Net income

Is this possible?

Dimensions:

1. Company

2. Rpt section (Calculated dimension) [Values: Actual 2013-5; Actual 2012 - 5]

=$(=[Rpt sectie])

[Rpt Sectie] contains

'Actuals ' & chr(10) & Date($(vMaxDate),'YYYY/M')

(CP)

and

'Actuals ' & chr(10) & Date(AddYears($(vMaxDate),-1),'YYYY/M')

(CPLY)

vMaxDate retains selected period or the period we live in

SET vMaxDate = Date(Floor(
                                                             MonthEnd(
                                                                                           RangeMin(
                                                                                                                aggr(Max([Maand einddatum]), KPI), Today()
                                                                                                                )
                                                                                           )
                                                             )
                              )
;

3. [Column name] (Values: Revenue, Gross profit, Net Income)

Expression:

1. $(=Expression)

Expression filed contains the actual expression used for Column name

for Revenues for instance is

sum( {<
[Exec P&L Heading] = {'Total Revenue from Services'},
[F einddatum]={"=$(=$(=$(getMaxDate('P<->(P-1)'))))"}
>}
[#Transact bedrag])

ok. the good news it at works. The bad news is works but not as expected.

If i select [Rpt Sectie] and i change the order of the pivot's dimensions that [Rpt section] will come as last, it does work...

Otherwise, not...

what am im doing wrong???

Unfortunatly i cannot attached a sample as the qvw is too big, too complex and contains sensitive information...

topic set analysis expression in Pivot dimension in QlikView

set analysis expression in Pivot dimension