Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selected Month vs Previous Month

I am trying create an expression which calculated the difference between the selected month's costs and the one preceeding it.

Below is an explanation of my key fields:

  • [Charge Rate] - Column with spend I am trying to sum
  • [Data Feed Date] - Date formatted as 8/1/2012 12:00 AM
  • [Data Feed Month] - Date formatted as Aug 2012

I do not want to be tied to the most recent month (I want to be able to select which month I am viewing). I was able to create a label using the following formula (=AddMonths([Data Feed Month],-1)).

I tried applying this to my expression but it is not working. (sum({<[Data Feed Month]={'=AddMonths([Data Feed Month],-1)'}>}[Charge Rate]))

Any help would be greatly appreciated.

1 Solution

Accepted Solutions
Not applicable
Author

I found the solution from another discussion thread. It appears I needed to format as a date for it to work.

Below is the solution and the explanation from the other thread

(Sum({<[Data Feed Month]={"$(=date(AddMonths([Data Feed Month],0),'MMM YYYY'))"}>} [Charge Rate]))

You need to format the SelDate after you subtract the month. It doesn't preserve your special formatting, and is doing more of a text comparison than a numeric comparison. Also, once you do format it, you're going to have to put it in quotes or QV will probably get confused. So I'm guessing that this would work:

Sum({1<SelDate={"$(=date(AddMonths(SelDate,-1),'MMM YYYY'))"}>} DollarAmount)

View solution in original post

4 Replies
hallquist_nate
Partner - Creator III
Partner - Creator III

Have you tried using the function "GetCurrentSelection" or "GetCurrentField"?  I think you need to use GetCurrentSelection, on this. 

Give it a try and let me know...

Nate

Not applicable
Author

Getcurrentselection appears to list in words everything that is currently selected (like a current selection box). The problem is this lists everything and I am only concerned about what date I am filtered on and displaying the previous month.

hallquist_nate
Partner - Creator III
Partner - Creator III

Take a look at GetFieldSelections.  That might be what you are looking for. 

I also use this formula...

(Sum({$< BookedYear={$(=Max(BookedYear)-1)}>}  (ORDERED_QUANTITY*UNIT_SELLING_PRICE)))

In this case, you have to force the user to select a year, but you can do that by using the Conditional Expression so that the expression won't calculate unless a dimension value is chosen.   I avoided that by setting this tab to select Current year when activated. 

Will this suffice?

Not applicable
Author

I found the solution from another discussion thread. It appears I needed to format as a date for it to work.

Below is the solution and the explanation from the other thread

(Sum({<[Data Feed Month]={"$(=date(AddMonths([Data Feed Month],0),'MMM YYYY'))"}>} [Charge Rate]))

You need to format the SelDate after you subtract the month. It doesn't preserve your special formatting, and is doing more of a text comparison than a numeric comparison. Also, once you do format it, you're going to have to put it in quotes or QV will probably get confused. So I'm guessing that this would work:

Sum({1<SelDate={"$(=date(AddMonths(SelDate,-1),'MMM YYYY'))"}>} DollarAmount)