# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for
Did you mean:
Highlighted Contributor II

## Dimension order in pivot table ruining index calculation

Hi,

I'm trying to create an index chart, with some progress. This expression works in a pivot table as long as MonthYear is the second dimension (of 2)

SUM({<FMonth=,FY=,Year=,fMonth=,Month=,MonthYear=>}FTE)
/
(top(SUM({<FMonth=,FY=,Year=,fMonth=,Month=,MonthYear=>}FTE))/100)

However, I want to present this in a line chart, say with 'Staff Group' as the other dimension.  If MonthYear is the second dimension, each line in the chart represent a MonthYear, and Staff Group in on the X axis. If I swap the dimensions around, the calculations mess up.

Is there any way around this?

Many thanks.

Labels (5)

• ### sorting

1 Solution

Accepted Solutions
Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

Try this

Only({1}

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE))/100)

, [Staff Group], MonthYear)

)

11 Replies
Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

Try this

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear>} FTE))/100)

[Staff Group], MonthYear)

Highlighted Contributor II

## Re: Dimension order in pivot table ruining index calculation

Thanks Sunny. I should add that the MonthYear range is controlled with a calculated dimension (below). Using your expression restricts results to the latest month only, and the results  are different (bigger, not wildly so).

If(MonthYear >= AddMonths(Max(TOTAL MonthYear), -5), MonthYear)

Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

May be do it in the set analysis

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {"<=\$(=AddMonths(Max(MonthYear), -5))"}>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {"<=\$(=AddMonths(Max(MonthYear), -5))"}>} FTE))/100)

, [Staff Group], MonthYear)

Highlighted Contributor II

## Re: Dimension order in pivot table ruining index calculation

Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

How is MonthYear created in the script?

Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

Also, I added the incorrect sign <=... should be >=

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=AddMonths(Max(MonthYear), -5))"}>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=AddMonths(Max(MonthYear), -5))"}>} FTE))/100)

, [Staff Group], MonthYear)

Highlighted Contributor II

## Re: Dimension order in pivot table ruining index calculation

Still nothing.MonthYear is created in the mastercalendar

Date(MonthStart(PeriodDate),'MMM-YYYY') as MonthYear

Highlighted MVP

## Re: Dimension order in pivot table ruining index calculation

Try this

Aggr(

Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE)
/
(Top(Sum({<FMonth, FY, Year, fMonth, Month, MonthYear = {">=\$(=Date(MonthStart(Max(MonthYear), -5), 'MMM-YYYY'))"}>} FTE))/100)

, [Staff Group], MonthYear)

Highlighted Contributor II

## Re: Dimension order in pivot table ruining index calculation

Now shows the latest (selected) MonthYear, with the correct value against each Staff Group. The expression is calculating correctly, but needs to display the previous 5 months.

Thanks for looking at this.