Set Analysis

[stabben23]

Hi I have this problem.

I have Budget and Invoiced. In my model these are NOT concatenated into the same table and they wont be.

So I´m looking for a set Analys to find a solution for this. I need that PeriodYYYYMM "knows" that it should be the same as in BudgetMonth for all BudgetMonth.  sum({$<PeriodYYYYMM={"$(=(BudgetMonth))"}>}[Recorded amount]) is my expression.

If I select one BudgetMonth it will works but I want to se all Month.

These 2 fields dont have any connection to each other, both are comming from dimension tables in my model.

[MK_QSL]

Can you please share your sample data file in probably excel format with reduced data....?

[preminqlik]

HI ONCE TRY WITH

sum({$<PeriodYYYYMM={"$(=MAX(BudgetMonth))"}>}

[stabben23]

Hi,

works only if a select one month at the time. if I dont do any selection it will gives me 201412, not correct.

[simondachstr]

You need to use the =concat() function in order to create a string which includes all months to display. This you can then put into set analysis.

Something like:

sum({$<PeriodYYYYMM={"$(=Concat(distinct BudgetMonth,','))"}>}

Play around with the syntax first, though, since I'm not sure the above will work straight away.

[richard_pearce6]

Looking at the data model I think Martin Mahler  solution will work.

Why not just create a field PeriodYYYYMM in Budgets with the same value as BudgetMonth  and created a link?

You could also try =sum({$<PeriodYYYYMM=BudgetMonth}> }  [Recorded amount] )

Richard

QlikCentral.Com

[stabben23]

Hi,

Yes I have test this, concat will gives me ALL (the total) of Invoiced in every BudgetMonth field.

I still need to aggregate this value down to Month level.

[Michiel_QV_Fan]

Consider using a date island.

In a date island you have an unrelated calendar with all possible dates between a start and end date.

A selection from a date island dimension is passed to the expression with set analysis, like:

sum( {<Budgetdate={">=$(=min(DateIslandID))<=$(=max(DateIslandID))"}>}amount)

sum( {<Actualdate={">=$(=min(DateIslandID))<=$(=max(DateIslandID))"}>}amount)

// Load the Date Island

Let vStartDate=Floor(MakeDate(1921));

//Let vEndDate=Floor(MakeDate(2014,12,31));

let vEndDate = Floor(YearEnd(today()));

Let vDiff=vEndDate-vStartDate+1;

DateIsland:

Load

  date(TempDate) as DateIslandID,

  class(Year(TempDate), 10) As Jaar_groep,

  Year(TempDate) as Jaar,

  Year2Date(TempDate, 0, 1) as CurrentYtd,

  num(Month(TempDate)) As Maandnr,

  week(TempDate) as Week,

  ceil(month(TempDate)/3) as Kwartaalnr;

Load

$(vStartDate)+RecNo()-1 As TempDate

AutoGenerate($(vDiff));