R12 set analysis problem incorrect values

[#1]

I'm stuck here. Any suggestions? Trying to create a R12 based on the guide here:

The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.

This works fine getting previous months, any month:

=SUM

(

{$<

MC_YearMonthID={$(=Max(MC_YearMonthID)-1)},

MC_Year=,

MC_Quarter=,

MC_Month=,

MC_YearMonthDate=

>}

Sales

)

A YTD calculation as well:

=SUM

(

{$<

MC_YearMonthID={"<=$(=Max(MC_YearMonthID))"},

MC_Year = {$(=Max(MC_Year))},

MC_Quarter=,

MC_Month=,

MC_Week=,

MC_YearMonthDate=

>}

Sales

)

But this R12 gives me far to small numbers:

=SUM

(

{$<

MC_YearMonthID={">=$(=Max(MC_YearMonthID)–11)<=$(=Max(MC_YearMonthID))"},

MC_Year=,

MC_Quarter=,

MC_Month=,

MC_Week=,

MC_YearMonthDate=

>}

Sales

)

Anybody that sees any syntax problems?

I use a master calendar with:

TempDate As MC_YearMonthDate,

Week(TempDate) As MC_Week,

Year(TempDate) As MC_Year,

Month(TempDate) As MC_Month,

ApplyMap('QuartersMap', month(TempDate), Null()) as MC_Quarter,

(Year(TempDate)-1)*12+Num(Month(TempDate)) as MC_YearMonthID

[#2]

Hi Hakan I cant see anything immediate but what happens when you put the expressions

$(=Max(MC_YearMonthID)–11)

and

$(=Max(MC_YearMonthID))

into variables, and using the variables in the set analysis instead?

[#3]

Thank you, Erica!

The syntax seems correct so I will use your suggestion and test with variables.

[tresesco]

Could you post your sample app?

[Gysbert_Wassenaar]

edit: never mind.

I think the problem is how you create MC_YearMonthID:

(Year(TempDate)-1)*100+Num(Month(TempDate)) as MC_YearMonthID

You will get values like 201401. What you get if you subtract 11 from that is 201390... which won't map to any real month and dates. So you get results that are too small.

You can use the autonumber function instead:

autonumber(monthstart(TempDate),'MonthCounter') as MC_YearMonthID.

Note: for this to work the dates in your master calendar need to be ordered chronologically.

To make things easier you could also use the qvc script library to generate your calendars. It will generate fields like above automatically and also a lot of very useful set modifiers for use in your set analysis expressions. See this discussion: Tutorial - Using Common Date Dimensions and Shared Calendars

[#6]

Thanks Hakan!

Let me know how it goes.

Regards,

Erica

[#7]

Not so well.

Can't get it right putting set expression in a variable.

Not like this at least:

let vMinusEleven = {"=$(=Max(MC_YearMonthID)–11))"};

I've tried a few more variants.

[#8]

Now I know the reason. The original date field exists for the full year 2014.

Therefore I get last month of 2014 from:

MC_YearMonthID={$(=Max(MC_YearMonthID))}

R12 numbers then get quite smaller than expected, only sales figures for one month so far this year.

What would you suggest to do?

Modify master calendar so it maxes out in current date or modify set analysis selectors?

DateLinkTable:

LOAD

YearMonthDay,

Date(Date#((YearMonthDay),'YYYYMMDD'),'YYYY-MM-DD') as

MC_YearMonthDate Resident LinkTable;

[sebastiandperei]

Dear Gysbert, the result of (Year(TempDate)-1)*12+Num(Month(TempDate)) as MC_YearMonthID  aren't 201401, 201402....

I made calculations, and returns values like 24145 for 2014/Jan.

[sebastiandperei]

Hakan, could you send a qvw sample?

Sorry but i can't understand your last message.