topic Re: Analyzing Current and Previous Year data (See description) in QlikView

Analyzing Current and Previous Year data (See description)

Anonymous — Tue, 20 Oct 2015 06:17:05 GMT

I have the data set where it has Year as a dimension. It has data for the last 10 years. Say from 2005 to 2015.

My client always wants to analyse the current year's data and previous year data.

Say, I have a Bar Chart which shows the Sales of each Product for 2014 and 2015. For Time being I just used a simple Calculated dimension =If(Year = 2014 or Year=2015,Year) so that other data will be suppressed as NULL.

Not just the above scenario. I have used {<Year=2015>} in various expression which I need to convert to current available Year data.

The least, I could think of is max(Year) and max(Year)-1. Is there any alternatives available for this?

Can some one throw a light on this about how to proceed with this problem? so that when I do incremental load, the current data and previous year will be taken.

Re: Analyzing Current and Previous Year data (See description)

ecolomer — Tue, 20 Oct 2015 06:19:06 GMT

Use a variable with value = Max(Year)

Re: Analyzing Current and Previous Year data (See description)

Anonymous — Tue, 20 Oct 2015 06:20:23 GMT

Hi. Yes. I thought about that and also added that to my question. But is there any other way? Like using variables?

Re: Analyzing Current and Previous Year data (See description)

Anonymous — Tue, 20 Oct 2015 06:28:28 GMT

Also. When I use max(Year) inside set, there is some error in my set expression. How to overcome that?

Re: Analyzing Current and Previous Year data (See description)

ecolomer — Tue, 20 Oct 2015 06:30:29 GMT

Can you upload file qvw or an example with your data?

Re: Analyzing Current and Previous Year data (See description)

Tue, 20 Oct 2015 09:38:15 GMT

Hello,

You may do this with Set Analysis and variables. You need:

- Variable var_CurrentYear = GetFieldSelections(Year,True())

- Variable var_LastYear = GetFieldSelections(Year,True())-1

- Expression for this year (Example): =SUM({$<Año={'$(var_CurrentYear)'}>}[Sales])

- Expression for previous year (Example): SUM({$<Año={'$(var_LastYear)'}>}[Sales])

- Set the Calculation Condition of the objects using this Set Analysis to 'if(GetSelectedCount(Year,True())=1,1,0)' so it won't 'crash' when more than one year is selected.