Logic

[smilingjohn]

Hi Expertise

I  have a line chart Iam using the below expression to display the number of months.

Sum({<Month = {">=$(=Max(Month)-8) <=$(=Num(Month(Today())))"}>} Tickets)

The above  expression will display me exactly 9 months  and also tested for 9 , 10 , 111 , 12 months accordingly..

But this is not giving me 13 months..

if i use the below condition

Sum({<Month = {">=$(=Max(Month)-13) <=$(=Num(Month(Today())))"}>} Tickets)

by using the above expression still i get the line chart diplayed from 1 to 12 ...where as it has to display from 12 to 12 ( dec-2016 to dec2017)

as shown in the screenshot i have year from 2010 till 2017 and this is the month in number i have ..

in dimension also i am using Month..

How do i achive this ..need your help

Thanks in advance

[devarasu07]

Hi,

for the 13 month rolling you can try to user YearMonth field instead of Month

try to create year month at back end script,

Date(Date(Date#(Month,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth,

and u r set expression like below,

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -13), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), -1),  'MMM-YY'))"} >} Tickets)

Thanks,

Deva

[tresesco]

I would rather use Date field like:

Sum({<Date = {">=$(=MonthStart(Max(Date),-13)) <=$(=MonthStart(Today(),-1))"}>} Tickets)

[smilingjohn]

Hi Devarasu ..

I have the below format date ..as shown in screenshot

And iam trying to convert it like this but this year field is showing blank ..

do i need to make any changes ..

[smilingjohn]

Hi Devarasu ,,

Iam trying to restrict the Sold Date by using this login in the script is this correct?

It reloads but doesnt show any out put ..

Please suggest

where Sold_Date > date(1/12/2015,'DD/MM/YYYY' and Sold_Date <= date(1/12/2017,'DD/MM/YYYY'));

[YoussefBelloum]

Hi,

First you need to create the Month_Year format field date like this:

Date(MakeDate(year,month),'YYYYMM') as Date

after that you need this expression:

Sum({<Date ={">=$(=Date(addmonths(Max(Date), -13),'YYYYMM')) <=$(=Date(addmonths(Max(Date), -1),'YYYYMM'))"} >} data)

find attached:

[devarasu07]

Hi,

Rolling 13 month using set analysis

Sum({<Year=, Quarter=, Month=, Week=, Date={'>=$(=MonthStart(Max(Date), -13))<=$(=Date(Max(Date)))'}>} Tickets)

for the script method you can try restrict data like below,

let hDate = date(today()+(day(today())-1));

let dDate = date(MonthStart(today(),-12));

load * FROM table.qvd (qvd) where date(Date)>=date('$(dDate)') and date(Date)<=date('$(hDate)');

more steps  check this thread.

Last 3 months' data in load script