topic Problems with time interval month in QlikView

Problems with time interval month

Fri, 03 Apr 2015 16:13:48 GMT

Hello everyone,

little problem with a formula

Sheet "Sales Report"

Object "Budget"

Last column

I want to calculate the% increase in 2015 vs. 2014 but the same period.

The interval must be selected every month and in 2013, 2014 and 2015.

I want the value in%, for example, today April the ratio between January February March 2015 versus January February March 2014 .... and so on ... in May the ratio of January February March April 2015 versus January February March April 2014

Someone can help?

I attach files.

Thank You.

Gian Paolo

Re: Problems with time interval month

ramoncova06 — Fri, 03 Apr 2015 16:44:42 GMT

add into your script a regular date format and then create a variable that reads your max and min date and add that into your set analysis

sum({<FV_Tipo=,YearMonthDate = {'>=$(=(vMinCurrentDate))<=$(=(vMaxCurrentDate))'},>} FV_Imponibile)

this document should help also

QlikView App: Dates, Date Ranges and Set Analysis

Re: Problems with time interval month

Fri, 03 Apr 2015 18:00:41 GMT

Hi Ramon,

thanks but don't work ...

Please you can enter a formula in my example?

Thanks again ....

Gian Paolo

Re: Problems with time interval month

ramoncova06 — Fri, 03 Apr 2015 19:43:47 GMT

I misunderstood your requirement, you are already doing what I was suggesting

but have you tried using the column number identifier, instead of your current formula ?

this is the expression

(rangesum(above(column(1)&' ' ,0,12)))

rangesum(above(column(2),0,12))

and this is the result

Mese	2015	2014	Diff	Diff %	New Metric
	€ 129097.90	€ 241611.70	-€ 112513.80	+53.4%	53.43%
gen	€ 56650.00	€ 147613.70	-€ 90963.70	+38.4%	38.38%
feb	€ 87132.90	€ 219006.70	-€ 131873.80	+42.7%	39.79%
mar	€ 129097.90	€ 241611.70	-€ 112513.80	+185.6%	53.43%

Re: Problems with time interval month

Fri, 03 Apr 2015 20:59:27 GMT

Hi Ramon,

sorry but I explained bad ... we try again.

Sheet "Sales Report"

Object "Budget"

Last column

I select all of 2013, 2014 and 2015.

In the last column I want the following result.

Today we are in April and I would like the difference in % of sales between the months of January February March 2015 VS January February March 2014.

In May I would like the difference in % of sales between the months of January February March April 2015 VS January February March April2014.

Thanks.

Gian Paolo

Re: Problems with time interval month

ramoncova06 — Sat, 04 Apr 2015 22:23:44 GMT

‌what do you expect to see in Jan?

Re: Problems with time interval month

Sat, 04 Apr 2015 23:06:40 GMT

Hi Ramon,

look this formula:

(rangesum(above(sum({<FV_Tipo=, Anno={$(vMaxAnno)}>} FV_Imponibile),0,12)) - rangesum(above(sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}>} FV_Imponibile),0,12))) / rangesum(above(sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}>} FV_Imponibile),0,12))

Is almost correct but I want today April I make a comparison between the first three months of 2015 versus the first three months of 2014.

As it is now compares the first 3 months of 2015 with the end of 2014 and it is not what I want.

it is clear ... thanks

Gian Paolo

Re: Problems with time interval month

Sun, 05 Apr 2015 07:54:12 GMT

Hi Ramon .....

or better the formula works if I enter dimension "MONTH" and I become the first.

But if I enter this dimension "MONTH" throughout the grid changes and it is not what I want.

Thank You.

Gian Paolo

Re: Problems with time interval month

ramoncova06 — Tue, 07 Apr 2015 01:19:15 GMT

so i replaced the accumulation calculation that was being done in the first two columns with your expression and then applied the formula that I believe you wanted to add in

attached is the document

thanks

Re: Problems with time interval month

Tue, 07 Apr 2015 08:02:06 GMT

Hi Ramon,

I saw that you made changes to the object "Sales Report" of the sheet "Sales Report".

But that's okay.

The last column

I want to change the last column of the grid the object's Budget in the sheet "Sales Report".

OK?

Thanks

Re: Problems with time interval month

ramoncova06 — Tue, 07 Apr 2015 19:42:53 GMT

Try with these formulas

sum({<FV_Tipo=, Anno={$(vMaxAnno)},Annomese = {"<=$(=(max(Annomese,2)))"}>} FV_Imponibile)

sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)},Annomese = {<=$(=date(AddYears(max(Annomese,2),-1),'YYYY/MM'))"}>} FV_Imponibile)

you can switch the advanced search to variables

Re: Problems with time interval month

Tue, 07 Apr 2015 20:24:37 GMT

Hi Ramon, these formulas don't work ... sorry and thank you.

I would use the formula below but it does not work ... I also tried to replace, with 0.12, 0.3, (jan - mar) but the result does not change any other ideas?

The values in red indicate all 12 months?

and if you want only the first 3 months? I tried to put, 0,3 but the result does not change ...

Thank you ....

Were are you from?

Gian Paolo

Re: Problems with time interval month

Anonymous — Tue, 07 Apr 2015 22:05:28 GMT

Maybe!

Re: Problems with time interval month

ramoncova06 — Wed, 08 Apr 2015 03:01:13 GMT

Gian Paolo,

this is doing it for me, I am seeing the same results as in the previous changes that I had done

Re: Problems with time interval month

Wed, 08 Apr 2015 13:15:34 GMT

Hi Ramon,

seems to work!!!!

Thanks for your patience and availability ....

Gian Paolo