topic Compare two periods. in QlikView

Compare two periods.

Thu, 01 Jul 2010 13:31:12 GMT

Hi,

I have a little problem concerning a calculation with periods of dates

I does a selection into a period between 14/11/09 and 15/05/10.

My statistic is good for the calculation into the period (sum(Amount)=>Ok

I would like to compare this calculation with the same period the past year(14/11/08 --> 15/05/09)

I am a beginner into Qlikview and it's impossible to find the formula that will compare with the previous period.

Can you help me?

Thanks a lot

Compare two periods.

Thu, 01 Jul 2010 14:33:33 GMT

It could be something like this:


sum({1 <Date={"$(='>=' & AddMonths(min({$} Date), -12) & '<=' & AddMonths(max({$}Date), -12))"}>} Amount)

See the example in attachment

Compare two periods.

Thu, 01 Jul 2010 15:10:36 GMT

That's great !

All is ok !

Thanks a lot for your help

Ciao

Compare two periods.

Thu, 29 Jul 2010 14:37:21 GMT

Hi Nick and all,

I have a little problem with this expression because, since I put this calculation, the filters selected (Products,...) are not executed.

The filters are only applied into the columns without his calculation.

Can you help me?

Ciao

Michael

Compare two periods.

suniljain — Thu, 29 Jul 2010 14:44:21 GMT

Dear Martelmi,

please post your qlikview application.

Regards

Sunil Jain.

Compare two periods.

Thu, 29 Jul 2010 15:38:30 GMT

Here is an extract of the file.

Thanks a lot for you help

Ciao

Compare two periods.

Thu, 29 Jul 2010 17:02:32 GMT

Try this:

=sum({<DATELIV={"$(='>=' & AddMonths(min({$} DATELIV), -12) & '<=' & AddMonths(max({$}DATELIV), -12))"}>} CAFACTURE)

Compare two periods.

Thu, 29 Jul 2010 17:32:20 GMT

Hi,

If I keep my period, the amount is null.

The '1' is essential, in my opinion( What's this number? :-))

The problem is often when the statistic does more 1 column(In the example that I sent, the problem is resolved (also more columns)but in my real file, it's not correct)and when the table is dynamic .

If you want, i can send you another file with more details but I have to work in this file(delete curtain data 🙂 ).

Thanks a lot

Compare two periods.

Fri, 30 Jul 2010 13:15:11 GMT

If you are getting null it means there is no any data for previous period - check your data and conditions.

About 1 in Set Analysis:

There is one constant that can be used to denote a record set; 1. It represents the full set of all the records in the application.

The $ sign represents the records of the current selection. The set expression {$} is thus the equivalent of not stating a set expression. {1-$} is all the more interesting as it defines the inverse of the current selection, i.e. everything that the current selection excludes.

Compare two periods.

Mon, 02 Aug 2010 14:42:57 GMT

I check my data and I have data in the previous period.I join you the file with a particular case.

If I select one product, data , without the formula, are refreshed correctly.

But the problem is that the data, in the previous period, are refreshed for all products.

Is it possible to look this if you have any idea?

I searching in the help , in forums...

Can you help me?

Ciao

Compare two periods.

Tue, 03 Aug 2010 13:21:33 GMT

Think about how you calculate previous period. Do you think if you are looking at the data today the previous period will be today - 12 month.

According to the formulas the data dictates what the current period is. Take a look at attached doc. As you can see if you pick NOARTI = 27802, the current period will be from20/01/2009 to 22/02/2010, according to this previous period will be 20/01/2008 to 22/02/2009. And check now you data. Do you really have data for this previous period?

Maybe you need to tie your data to some specific Start/End period variables.

Compare two periods.

Tue, 03 Aug 2010 16:59:34 GMT

I sent you a new file, if you want, with good data. 🙂

The aim, in this report, is to have a preview of the whole of sales into a period.

The user can be filter product,.... but, if you look my file, I have filtered for the product 27675 and the customer 00307 !

I have data (If I keep the 1) but I have results for all products (for the past year)!

The start/end period variables have been a great help to see that data are not correct 🙂

Thanks a lot.

Compare two periods.

Wed, 04 Aug 2010 13:37:11 GMT

I have absolutely the same discussion on another post http://community.qlik.com/forums/p/32041/124682.aspx

All what you need is two variables for start and end period and use them in your expressions for set analysis instead of min()/max() functions.

In that post, I've made an example with four variables for two independent periods. In your case you have just current period and previous with 12 month offset. It will be enough to have just two variables for current period only.