Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
amithmurali
Contributor II

How to filter the data

Hi guys,

Suppose in my table,

i want to show comparison of this year data with the previous year data. when i compare it in  a gauge. its taking the data for the entire previous year and comparing with the 4months of the current year.

i have data for the entire year 2014 and till may for 2015. when i compare it should make a division like "2015 data till may / 2014 data till may".

1 Solution

Accepted Solutions
amithmurali
Contributor II

Re: How to filter the data

vMaxYear:   =Year(Max(Date))

vPriorYear     =(year(Max(Date))-1)

vMaxMonth     =max({<Year={'$(vMaxYear)'}>}Month)

vMinMonth     =min({<Year={'$(vMaxYear)'}>}Month)

The set analysis that i made is

sum({<Year={'$(vMaxYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)/

sum({<Year={'$(vPriorYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)

5 Replies

Re: How to filter the data

Hi

Try like this

vMaxMonth  <-- its store the May month value (i.e. Max month value of the current year)

Sum({<Year = {$(=max(Year))}, Month = {"<=$(vMaxMonth)"}>}Sales) /

Sum({<Year = {$(=max(Year)-1)}, Month = {"<=$(vMaxMonth)"}>}Sales)

amithmurali
Contributor II

Re: How to filter the data

Can you explain in detail. i am new to qlikview.

sujeetsingh
Honored Contributor III

Re: How to filter the data

Just try to study all about Set Analysis

amithmurali
Contributor II

Re: How to filter the data

vMaxYear:   =Year(Max(Date))

vPriorYear     =(year(Max(Date))-1)

vMaxMonth     =max({<Year={'$(vMaxYear)'}>}Month)

vMinMonth     =min({<Year={'$(vMaxYear)'}>}Month)

The set analysis that i made is

sum({<Year={'$(vMaxYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)/

sum({<Year={'$(vPriorYear)'},Month={'>=$(vMinMonth)<=$(vMaxMonth)'}>}Value)