Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Last 12 months

Hallo everybody,

I'm trying to show on the graph last 12 months (only) and till now I tried to work with calculating dimension. As it didn't work I thought to add new position (last12Months) in script. Could anybody help? In attachment my Calendar;-)

Thnak you in advance,

Beata Jablonska

14 Replies
Not applicable

Last 12 months

Hi,

i suggest you to use "set analysis" in expression. I attached qv application with this expression in it.

I hope this will help you Smiley Happy

Not applicable

Last 12 months

Dear Milda,

Could you send it as an expression?

I'm grateful,

Beata

Not applicable

Last 12 months

My point is, that I could't use set analysis (at least in the way I know it). I'm calculating expression by Sum (invoiceAmount) and aggregate it 12 steps back (so I can show Moving Annual Total). When I want to show recent 12 months, the graph is useless, as it starts to calculate expression from the first shown month (instead of keeping old aggregation) I tried few things, and nothing helps, so I though script could be an idea.

Above the graph I need:

Not applicable

Last 12 months

sum(
{$<
CalendarDate={'*'},
CalendarDay={'*'},
CalendarMonthAndYear={'*'},
CalendarQuarter={'*'},
CalendarWeek={'*'},
CalendarWeekAndYear={'*'},
CurYTDFlag={'*'},
WeekDay={'*'},
CalendarYear={'*'},
CalendarMonth={'*'},
CalendarDate={$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}
>}
1)

Here i used sum() function as an example. You can use any other function instead.



Not applicable

Last 12 months

So you need that, for example, at column "1-1-2010" would be the sum of "1-1-2009.....1-1-2010" periods?

Not applicable

Last 12 months

Dear Milda,

Firstly thank you for your effort. Secondly, unfortunately it doesn't worlSmiley Sad I have error:

Error in expression:
{ is not a valid function

MasterCalendar:
LOAD
CalendarDate={(internal error)}>}1,

I put expression as following:

MasterCalendar:
LOAD
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
Week(TempDate) AS CalendarWeek,
Weekday(TempDate) AS WeekDay,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
CalendarDate={$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}>}1)

I hope you can help me,

Greetings,

Beata

Not applicable

Last 12 months

Yes, at column `1/1/2010` I should have a sum for last 12 months so, from February 2009:-)

msteedle
Contributor

Last 12 months

One solution is generating a rolling months table to associate to your calendar table. Please see the attached.

One note: I changed how you were generating CalendarMonthAndYear so it could be sorted numerically.

Not applicable

Last 12 months

Dear Michael,

Thank very much for this file! The only thing is, that now maximum date is December 2010 (insted of current- January), so it's not exactly what I wanted. I am trying to find why maximum date is December 2010, and when it will be changed it will be perfect! If you see the mistake, please let me know.

Acha, and I would like to ask you for one more thing- could you next time copy/paste changes you made, insted of attaching a file? Unfortunately I'm still Personal Edition userSmiley Sad

Thank you in advance,

Best greetings,

Beata

Community Browser