topic Re: Selected Month vs Previous Month in QlikView

Selected Month vs Previous Month

Mon, 08 Oct 2012 16:12:47 GMT

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.

Re: Selected Month vs Previous Month

hallquist_nate — Mon, 08 Oct 2012 16:46:16 GMT

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

Re: Selected Month vs Previous Month

Mon, 08 Oct 2012 16:53:55 GMT

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.

Re: Selected Month vs Previous Month

hallquist_nate — Mon, 08 Oct 2012 17:05:34 GMT

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?

Re: Selected Month vs Previous Month

Mon, 08 Oct 2012 17:28:53 GMT

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)