Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

R12 set analysis problem incorrect values

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

14 Replies
Not applicable
Author

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?


Not applicable
Author

Thank you, Erica!

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

tresesco
MVP
MVP

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


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks Hakan!

Let me know how it goes.

Regards,

Erica

Not applicable
Author

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.

Not applicable
Author

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
Specialist
Specialist

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
Specialist
Specialist

Hakan, could you send a qvw sample?

Sorry but i can't understand your last message.