Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Anyone there online to reply for this post its little urgent.
Thanks
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
@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
@sasikanth Any other solution please
Anyone can help me out
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());
@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.
Thanks to all i got the solution for my requirements.
@sasikanth, @bgl-sjoyce thanks for replying me
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).