topic Re: Set analysis monthly trend in QlikView

Set analysis monthly trend

keitel2015 — Fri, 04 Sep 2015 16:46:09 GMT

Hi All,

I have a question regarding set analysis. I use this formula to get the revenue for the current or selected fiscal date:

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), ))<=$(=MonthEnd(Max([Fiscal Date])))'}>} [Revenue])

My formula to get the revenue for the month before is this:

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -1))<=$(=MonthEnd(Max([Fiscal Date])-1))'}>} [Recurring Revenue (Euro)])

It works very well but once I selected more than one date in fiscal date (e.g. Oct 2014 and Nov 2014) I don´t get the correct value for the month before my selected date (in this case Sep 2014).

Has anyone a solution?

Thanks.

Re: Set analysis monthly trend

maxgro — Fri, 04 Sep 2015 17:07:17 GMT

try here

Relative Calendar Fields

first expression in that post is like your (if you want you can adapt changing the fields names) but then Henric solved the problem adding a field in the calendar

Re: Set analysis monthly trend

keitel2015 — Mon, 07 Sep 2015 08:28:31 GMT

Thanks for your input but unfortunately it isn´t a solution for me.

Let’s assume you have all months of 2013 and 2014. If you click on e.g. Jun and Jul 2014 then your data sets will be decrease on your selection. What I want is to show the sum of Jun and Jul 2014 and the value before my selected date (May 2014).

What I currently have or what you suggested works fine if I only select single dates and no intervals (Jun AND Jul 2014). Because if you click on more than one date your data sets are only what you selected (Jun and Jul) so the formula will only consider Jun and Jul 2014 but not the entire data sets in my application.

For that reason I need a formula which identify the youngest selected date (Jun 2014) and then use the entire data sets to get the value before the youngest date.

Re: Set analysis monthly trend

sunilkumarqv — Mon, 07 Sep 2015 09:55:24 GMT

use selections fields in set analysis like below

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -1))<=$(=MonthEnd(Max([Fiscal Date])-1))'},

MonthField=,[Fiscal Date]=,Qtrfield=>} [Recurring Revenue (Euro)])

Re: Set analysis monthly trend

Mon, 07 Sep 2015 10:07:43 GMT

do as follows:

[Date]={">=$(=$(vFrom))<=$(=$(vTo))"}>}

create variable vFrom=MonthStart(Max([Fiscal Date]), -1)

vTo=MonthEnd(Max([Fiscal Date])-1)

Re: Set analysis monthly trend

keitel2015 — Mon, 07 Sep 2015 10:27:06 GMT

I changed the variables into:

MonthStart(Min([Fiscal Date]), -1)

MonthEnd(Min([Fiscal Date]), -1)

That works for me, thanks