topic Re: R12 set analysis problem incorrect values in QlikView

R12 set analysis problem incorrect values

Mon, 06 Jan 2014 21:03:03 GMT

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

Re: R12 set analysis problem incorrect values

Thu, 09 Jan 2014 23:59:41 GMT

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?

Re: R12 set analysis problem incorrect values

Thu, 16 Jan 2014 07:29:46 GMT

Thank you, Erica!

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

Re: R12 set analysis problem incorrect values

tresB — Thu, 16 Jan 2014 08:02:06 GMT

Could you post your sample app?

Re: R12 set analysis problem incorrect values

Gysbert_Wassenaar — Thu, 16 Jan 2014 08:37:56 GMT

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

Re: R12 set analysis problem incorrect values

Thu, 16 Jan 2014 12:42:01 GMT

Thanks Hakan!

Let me know how it goes.

Regards,

Erica

Re: R12 set analysis problem incorrect values

Fri, 17 Jan 2014 10:11:05 GMT

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.

Re: R12 set analysis problem incorrect values

Fri, 17 Jan 2014 13:20:24 GMT

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;

Re: R12 set analysis problem incorrect values

sebastiandperei — Fri, 17 Jan 2014 13:58:08 GMT

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.

Re: R12 set analysis problem incorrect values

sebastiandperei — Fri, 17 Jan 2014 14:00:54 GMT

Hakan, could you send a qvw sample?

Sorry but i can't understand your last message.

Re: Re: R12 set analysis problem incorrect values

Fri, 17 Jan 2014 15:26:00 GMT

I now get correct R12 numbers for full year, like 2013.

For 2014 it does not work.

Also, I get correct number in a text box but when splitting over months numbers also split - how to get correct R12 in a year table with month as dimension?

I couldn't down-size the application so I have added it as a DropBox download.

https://www.dropbox.com/s/4yaf6we4ispfue0/FsgAnalys%20-%20Test%20R12%20Sample.zip

Re: Re: R12 set analysis problem incorrect values

sebastiandperei — Tue, 21 Jan 2014 19:19:17 GMT

Can you reduce?

Re: Re: Re: R12 set analysis problem incorrect values

Wed, 22 Jan 2014 06:13:26 GMT

Yes, here it is.

Re: R12 set analysis problem incorrect values

tresB — Wed, 22 Jan 2014 06:31:46 GMT

The figure 2417588,17189 that you see in your text box (that says, 'R12Sales Working') is actually, 2417588.17189.

Your decimal separator is ',' (comma), you can correct it in number tab. Does it clarify? Or, am I missing something?

Re: Re: R12 set analysis problem incorrect values

Wed, 22 Jan 2014 06:51:41 GMT

Thank you for your answer.

No, the number is correct as an R12 for 2014 december. It is here my problem is - I have a 'full' year from my linked table no matter what year I look at. It is the way this application was designed once. So max('2014') is december. I need to handle current year in my R12 so that I have a 'max' month as current month if year is current year. Does this makes sense?

Second problem is something different. If I use set analysis as above for R12 in a text box for any month in 2013 or earlier I do get a correct R12. But when using month as dimension in a chart box they are split. Please see qvw-file. How do I get a correct R12 here?