Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

keitel2015
New Contributor II

Set analysis monthly trend

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.

1 Solution

Accepted Solutions
Not applicable

Re: Set analysis monthly trend

do as follows:

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

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

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

5 Replies
MVP
MVP

Re: Set analysis monthly trend

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

keitel2015
New Contributor II

Re: Set analysis monthly trend

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.

sunilkumarqv
Valued Contributor II

Re: Set analysis monthly trend

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)])

Not applicable

Re: Set analysis monthly trend

do as follows:

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

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

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

keitel2015
New Contributor II

Re: Set analysis monthly trend

I changed the variables into:

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

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

That works for me, thanks

Community Browser