topic Re: Set Analysis in QlikView

Set Analysis

stabben23 — Thu, 10 Apr 2014 06:42:29 GMT

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.

Re: Set Analysis

MK_QSL — Thu, 10 Apr 2014 07:04:28 GMT

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

Re: Set Analysis

preminqlik — Thu, 10 Apr 2014 07:59:00 GMT

HI ONCE TRY WITH

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

Re: Set Analysis

stabben23 — Thu, 10 Apr 2014 08:19:38 GMT

Hi,

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

Re: Set Analysis

simondachstr — Thu, 10 Apr 2014 08:24:35 GMT

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.

Re: Set Analysis

richard_pearce6 — Thu, 10 Apr 2014 08:34:25 GMT

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

Re: Set Analysis

stabben23 — Thu, 10 Apr 2014 08:35:07 GMT

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.

Re: Set Analysis

Michiel_QV_Fan — Thu, 10 Apr 2014 08:36:35 GMT

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