Pivot table, Dimension, set analysis

[#1]

Hi!

In my pivot table set analysis ignore Dimensions. I don't now why.

Each portfolio has a set of positions. For each item, there is the concept of the gain. For a portfolio is the mean value of Heine products.

The formula depends on the selected date range and has been developed in QV.

task:

Make a pivot table showing the change in the average value of gain on the portfolio from time to time for all time of its existence.

data: (dimensions in pivot table is yellow)

problem:

Counts only all of value dimension

For Example Expression:

Sum(aggr(Sum({$<PositionHistoryDate = {'$(=Max(RangeEndDate))'}>}PositionHistoryValue),$PositionId))

Where RangeEndDate associated with MonthName, QuarterName and YearName. Why it's not work? How I can do this?

[marcus_sommer]

I think you will need to link your "MonthTable" table with your datamodel then calculations over loosen tables aren't recommended. See also:

The Master Calendar

Canonical Date

- Marcus

[#3]

Sorry, but it doesn't work.

I think, I have problem with set analysis into aggregation function. For some reason it does not take into account dimensions and calculate Max(RangeEndDate) for all MonthTable. Maybe you know how I can fix this?

[#4]

Since your Dimension table (Month Table) is not linked with rest of the data that is why your expressions not working as expected.

The best solution Marcus_Sommer‌ has given of master calendar.

But if you still need to use the current model, then in your expressions you need to use your dimensions like:

if(MonthName = 'Jan' , sum({<DateField = {use MonthStartdate for Jan and MonthEndDate for Jan}>}A),

     if(MonthName = 'Feb' ....

where DateField is the field in your fact.

Hope this works or is helpful.

Thanks,

Singh

[#5]

Ok.

I hoped that by using MonthName, QuarterName and YearName in dimension pivot table was to limit the sample in set analysis ({$<PositionHistoryDate = {'$(=Max(RangeEndDate))'}>}), but it not happen. It not work because table MonthTable is not linked to main data?

I don't understand why.

ps MonthName, QuarterName and YearName, RangeStartDate and RangeEndDate are in the same table MonthTable.

[#6]

You are expecting SUM of PositionHistoryValue field, with fields which are not related to it.

It will work for

if you use any of the below as Dimension:

MonthName

QuarterName

YearName

Expression Fields(with count() or only() or other aggr functions) :

RangeStartDate

RangeEndDate

So if you need to use any other field from the data model, then you need to have a link with your data model.

Hope this is helpful.

Thanks,

Singh

[#7]

I change my data model, but value not changed.

I think problem not in a data model.

I think problem in set analysis and function aggr()

[marcus_sommer · Accepted solution]

The problem isn't the aggr() or set analysis - it is the datamodel which shouldn't have any synthetic keys (unless you knows exactly what you are doing - it shouldn't be appear by accident). This meant you should have only one field which is connected between the table "MonthTable" and "Positions" or "Incomes", maybe by loading the field "MonthName" from "MonthTable" twice and convert and rename it like the field "YearMonth" from "Incomes". See also:

Synthetic Keys

- Marcus