Month Math within set analysis

[#1]

Hello,

I have a report where a n user selects the year and month (Jan-Dec) to see monthly Rev. Within the report, I use set analysis to also see the same month selected, but for the year previous year , to have a side by side monthly year over year comparison. The equation I am using for that is below.

=Sum({$<OrderCreateDateYear={"$(=max(OrderCreateDateYear)-1)"}>} Revenue)

I am attempting to add another column that shows the rev for the month before the selected month. This is what I am having trouble with. The equation below is not working.

=Sum({$<OrderCreateDateMonth={"$(=max(OrderCreateDateMonth)-1)"}>} Revenue).

i think that this may be due to the fact that month is in Jan, Feb, Mar.....format. However , when I export the month list box to excel I get numeric values (1-12) , which makes be think the above equation should work.

Any Help is appreciated.

Thanks,

Jeff

[#2]

Hi Jeff, you can accomplish this using the monthyear funtion with the offset parameter. This function automatically does all the sanity validations for you, for example, if the user selects jan-2010 it will return the previous month as dec-2009.

Your expression should look similar to the following:

Sum({$<OrderCreateDateMonthYear={"$(=monthyear(max(OrderCreateDate),-1))"}>} Revenue).

[#3]

Hi Ivan,

Thanks for your quick response. I am assuming that OrderCreateDateMonthYear is a field I should create using OrderCreateDate in my script , is that correct. If so should this field be formatted as 'MM/YY'?

Does the monthyear function turn ordercreatedate into this same format?...MM/YY?

Thanks,

Jeff

[#4]

I'm not sure Monthyear is a valid QlikView function. MonthName is a function which should return the month and year like: Sep 2010.

The key to working with dates is to figure out what you need the dollar sign expansion to return. To do this, create your expression, but hardcode a date in the field. This will tell you the format that the dollar sign expansion needs to return. Something like:

=Sum({$<OrderCreateDateMonth={"Sep 2010"}>} Revenue)

Or whatever format your OrderCreateDateMonth field is in.

Once you have a working Set Expression, it should help with the dollar sign expansion. To figure out what the dollar sign expansion is returning, create a chart with your expression, but don't give it a label. Then when the chart is rendered, the label will be your Set Expression with the dollar sign expansion evaluated. Compare this to what you needed to use in the hardcoded expression above. You may need to format the date (use Date()) to get it in the format QlikView is expecting.

It really comes down to how the data is formatted in your specific application.

[#5]

Hi jeff, as nmiller pointed out correctly monthyear is not a valid qlikview function, for some reason I was thinking in the return value rather than the function name. Regarding to your question, the answer is that you do have to create a field containing the month year of the date. This can be accomplis within the script by apply the monthname function to a date ( monthname(date) ).

So once, you have the field OrderCreateDateMonthYear created, you can use a similar expression to the following:

Sum({$<OrderCreateDateMonthYear={"$(=monthname(max(OrderCreateDate),-1))"}>} Revenue).

Regards