topic Rolling 30 days Values in App Development

Rolling 30 days Values

Rocky6 — Mon, 26 Jul 2021 05:29:35 GMT

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.

Re: Rolling 30 days Values

Rocky6 — Mon, 26 Jul 2021 06:11:46 GMT

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

Thanks

Re: Rolling 30 days Values

sasikanth — Mon, 26 Jul 2021 06:29:02 GMT

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

Re: Rolling 30 days Values

Rocky6 — Mon, 26 Jul 2021 08:27:06 GMT

@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

Re: Rolling 30 days Values

Rocky6 — Mon, 26 Jul 2021 14:57:08 GMT

@sasikanth Any other solution please

Anyone can help me out

Re: Rolling 30 days Values

bgl-sjoyce — Mon, 26 Jul 2021 16:42:13 GMT

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());

Re: Rolling 30 days Values

sasikanth — Mon, 26 Jul 2021 17:02:23 GMT

@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.

Re: Rolling 30 days Values

Rocky6 — Wed, 28 Jul 2021 08:59:36 GMT

Thanks to all i got the solution for my requirements.

@sasikanth, @bgl-sjoyce thanks for replying me

Re: Rolling 30 days Values

Kaushik2020 — Wed, 13 Dec 2023 10:18:29 GMT

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).