topic Re: Dimension order in pivot table ruining index calculation in QlikView

Dimension order in pivot table ruining index calculation

zakpullen — Sat, 16 Nov 2024 20:58:32 GMT

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.

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Fri, 26 Apr 2019 15:20:07 GMT

Try this

Aggr(

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

, [Staff Group], MonthYear)

Re: Dimension order in pivot table ruining index calculation

zakpullen — Fri, 26 Apr 2019 15:33:52 GMT

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)

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Fri, 26 Apr 2019 15:39:07 GMT

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)

Re: Dimension order in pivot table ruining index calculation

zakpullen — Fri, 26 Apr 2019 15:44:26 GMT

Unfortunately that produces no results at all!

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Fri, 26 Apr 2019 15:52:59 GMT

How is MonthYear created in the script?

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Fri, 26 Apr 2019 15:54:15 GMT

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)

Re: Dimension order in pivot table ruining index calculation

zakpullen — Fri, 26 Apr 2019 15:57:47 GMT

Still nothing.MonthYear is created in the mastercalendar

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

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Fri, 26 Apr 2019 16:26:21 GMT

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)

Re: Dimension order in pivot table ruining index calculation

zakpullen — Mon, 29 Apr 2019 08:12:10 GMT

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.

Re: Dimension order in pivot table ruining index calculation

zakpullen — Mon, 29 Apr 2019 11:53:11 GMT

Actually, the expressions work if MonthYear is the second dimension, but not if it's the first. It needs to be second for the top() function to work, but in a line chart MonthYear needs to be on the X-axis (which it can't be as second dimension).

Re: Dimension order in pivot table ruining index calculation

sunny_talwar — Tue, 30 Apr 2019 11:41:49 GMT

Try this

Only({1}

Aggr(

, [Staff Group], MonthYear)

)