Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Creator
Creator

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)
1 Solution

Accepted Solutions
sunny_talwar

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
sunny_talwar

Try this

Aggr(

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

[Staff Group], MonthYear)

zakpullen
Creator
Creator
Author

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)

sunny_talwar

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)

zakpullen
Creator
Creator
Author

Unfortunately that produces no results at all!

sunny_talwar

How is MonthYear created in the script?

sunny_talwar

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)

zakpullen
Creator
Creator
Author

Still nothing.MonthYear is created in the mastercalendar

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

sunny_talwar

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)

zakpullen
Creator
Creator
Author

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.