0

I have started working with QS recently and I have a problem with defining an expression which will allow me to display previous month's data.

I have tried creating previous month variable:

vPreviousMonth = MonthName(Addmonths(Max([Calendar Termination Month]),-1))

however this did not work - retrieves value 0. Then I tried below:

If(MonthStart([Calendar Termination Month])=MonthStart(Today()),-1,0) as PreviousMonth

which doesn't work either - retrieves current month's values, used in below expression:

sum({<PreviousMonth={"-1"}>} [Terminated]).

Lastly, I tried

sum({$<[Calendar Termination Month]={"$(=[Calendar Termination Month](AddMonths(Max(Date),-1),'YYYY-MM'))"}>}[Terminated])

which, surprise!, retrieved 0 value as well.

Is there any way I can somehow make this work?

great4whale
  • 19
  • 1
  • What type of values are stored in `[Calendar Termination Month]`? – Stefan Stoichev Apr 27 '21 at 15:55
  • In my dataset I had a column called `[Termination date]`, from which I have extracted month by using `Month([Termination Date]) as [Calendar Termination Month]`, as I read somewhere I should first have month extracted before trying to create previous month variable. – great4whale Apr 28 '21 at 05:54
  • You should try the addmonths() logic with the [Termination Date] field. The function requires a date not just the month portion to work – The Budac Apr 28 '21 at 07:49
  • Thank you for your remarks. I have tried `sum({$<[Termination Date]={"$(=[Termination Date](AddMonths(Max(Date),-1),'YYYY-MM'))"}>}[Terminated])` which retrieved 0 value. Also provided below `sum({$<[Calendar Termination Month]={"$(=month(AddMonths([Termination Date],-1))))"}>}[Terminated])` retrieved 0.... I am starting to suspect that first I need to do something with `[Termination Date]' column but I am unable to find any useful directions anywhere... – great4whale Apr 28 '21 at 14:34
  • If you can show us some sample lines of the data and expected results that might help us to help you – The Budac Apr 29 '21 at 14:01
  • What is the format of the [Termination Field]? Is it, for example, DDMMYYYY or MMM? – LuísA Apr 30 '21 at 11:05

1 Answers1

1

Try this

sum({$<[Calendar Termination Month]={"$(=month(AddMonths([Termination Date],-1))))"}>}[Terminated])
The Budac
  • 1,571
  • 1
  • 8
  • 10