Rolling 30 days Values

[Rocky6]

 Hi everybody ,

Hope all are good and fine.

I need a answer asap if anyone online please help me out.

 I have requirement where i need to display for a metric the below values .

Current month (running month) use rolling 30 days for Ex: Jul 18th refresh calculates the Metric value using  Start Dt field between Jun18 through Jul 18th.

 If user selects previous month for Ex: May, it will display May Month (May ME – May 1 through May31) .

Thanks in advance .

Please reply me.

[Rocky6]

Anyone there online to reply for this post its little urgent.

Thanks

[sasikanth]

HI @Rocky6 ,

you can try the below one,

Variables: 

vMonthType1=if( MONTH(max({1}[Trans Date]))=MONTH(max([Trans Date])),1,0)

vMonthType2=if( MONTH(max({1}[Trans Date]))<>MONTH(max([Trans Date])),1,0)

Expression:

=sum({<[Trans Date]={">= $(=AddMonths(max([Trans Date]),-1))<=$(=AddMonths(max([Trans Date]),0))"}>}Actual) * vMonthType1

+

sum({<[Trans Date]= {">=$(=monthstart(max([Trans Date])))<=$(=monthEnd(max([Trans Date])))"}>}Actual) * vMonthType2

Thanks, 

Sasi

[Rocky6]

@sasikanth  Trans date is what field which i am using in data model as start date ?

I tired its not showing up the value can u please let me how this variable works i am new to qliksense

[Rocky6]

@sasikanth  Any other solution please

Anyone can help me out

[bgl-sjoyce]

Create a StartDt calendar table with your required logic, joined on Start Dt.  The month in this calendar table is what you can use on your front-end; since you will have a many to many relationship for days at the end of previous month.

Below uses today(), but you may want to you substitute it with max(StartDt) that would be calculated first.  You also may want to not do this if today or max(StartDt) is the last day of the month as you have a full month of data, you can add that logic in.

Example...

//Rows for current month (rolling 30 days)

StartDateCalendar:

Load 

StartDt

,month(today()) as StartMonth

Resident <FactTable>

where StartDt >= date (today() - 30);

//Rows for previous months

Concatenate (StartDateCalendar)

Load

StartDt

,month(StartDt) as StartMonth

Resident <FactTable>

where StartDt < Monthstart(today());

[sasikanth]

@Rocky6 , 

 yes, you can replace [Trans Date] with Start Date , also try to include variables inside $ ().

=sum({<[Trans Date]={">= $(=AddMonths(max([Trans Date]),-1))<=$(=AddMonths(max([Trans Date]),0))"}>}Actual) * $(vMonthType1)

+

sum({<[Trans Date]= {">=$(=monthstart(max([Trans Date])))<=$(=monthEnd(max([Trans Date])))"}>}Actual) * $(vMonthType2)

it would be more helpful if you can share sample app or Data model SS. 

[Rocky6]

Thanks to all i got the solution for my requirements.

@sasikanth, @bgl-sjoyce  thanks for replying me

[kaushi2020]

Please use Date parameter as below.

Date={">=$(=date((max(Date)-29)))<=$(=date(max(Date)))"}>}

this date calculates last 30 dates based on the max(date).