Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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.

1 Solution

Accepted Solutions
Highlighted

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)

)

View solution in original post

11 Replies
Highlighted

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

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
Contributor II

Re: Dimension order in pivot table ruining index calculation

Unfortunately that produces no results at all!

Highlighted

Re: Dimension order in pivot table ruining index calculation

How is MonthYear created in the script?

Highlighted

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

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