Last Month Variable

[MPS]

Dear All,

have below expression which captures current month, but I need the last month. Sadly couldn't find any variable for last month. Can you please help

sum( {<RowType={'Other Activity'},[Other ActivityType]={'field Name'}, [Month Year]={"$(vMonth)"}
,YEAR=,MONTH=,Quarter=,[_KEY_DATE]=>} ActivityValue)

[JuanGerardo]

Hi @MPS, vMonth is not a Qlik Sense variable, it is a variable defined in the application design (reload script or UI). Probably its value is something like:  Month(Today())

If you want to have another variable for previous month, you can define a variable (more info here), for example vPreviousMonth, with the following expression: Month(AddMonths(Today(), -1))

JG

[MayilVahanan]

Hi @MPS 

If you want dynamic value for Last Month, try like below

 MonthName(AddMonths(Max(Date), -1))

If you want static, @JuanGerardo solution works for you.

[MPS]

Hi @MayilVahanan 

the expression gave me error. but expression given by @JuanGerardo  works, just that, when i try to select any months, the data shows for previous month. Like, tile shows data for Mar-2021, but when I select Feb-2021 from the filter, the KPI Tile shows data for Jan-2021 😞 

now I am confused, what to do. I want the KPI tile to show previous month data, but if a different month selected, data should show for that particular month. Do you have any idea please. 

[JuanGerardo]

Hi @MPS,  this is a new logic to be implemented in your measure: select previous month (to current date or to max loaded date) but select last available month if some date is selected. Probably the best way to apply this is checking for the max available date (based on your current filters), if it is the last date in your model show the previous month, else show selected month. This approach could be better than using getselected() function, in case you have different fields to selecting dates:

// If based on current date, but better to use Today(0), as this will consider as Today the date of data reload:

If(Month(Today(0)) = Month(Max(YourDateField),  // Max avalilable date

   Month(AddMonths(Today(0), -1)),   // Last month

   Month(Max(YourDateField))  // Current selected month

   )

// If based on the maximum date in your model

If(Month(Max({1} YourDateField)) = Month(Max(YourDateField),  // Max avalilable date

   Month(AddMonths(Max({1} YourDateField), -1)),   // Last month

   Month(Max(YourDateField))  // Current selected month

   )

JG