topic Re: Chart on the last 12 months in QlikView

Chart on the last 12 months

Fri, 14 Jun 2013 17:30:55 GMT

Hello ,

I ve met an problem ,

I m trying to build an chart which calculate an indicator (NBcustomer) on the last 12 months

Dimension : Month

Indicators : sum(NBCustomer)

I would like the last 12 months begin at the date of today() if no selection is done or or after when an month and an year is selecionned it s start at this month..

If you could help i ve to delevery in short time ...

Thank a lot

Re: Chart on the last 12 months

Gysbert_Wassenaar — Fri, 14 Jun 2013 17:38:05 GMT

See blog posts Relative Calendar Fields and Accumulative Sums and document Calculate rolling n-period totals.

Re: Chart on the last 12 months

Fri, 14 Jun 2013 18:07:56 GMT

Hello,

Thank for the answer and for the link but i ve tried to use the expression but it doesn't work and i prefer use the set analysis and not transform my calendar :

Sum({$<Date={">=$(=MonthStart(AddMonths(MaxDate),-12)))"}*{""<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

Do you know what is wrong is this syntax...

Thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 18:15:33 GMT

Try with this:

Sum({$<Date={">=$(=MonthStart(AddMonths(MaxDate),-12))) <$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

Re: Chart on the last 12 months

Fri, 14 Jun 2013 18:56:08 GMT

thanks it s working but the problem is if i selected an month my chart is updating and i only see the month

cause my dimension is month

and i would like to know if you think if it s possible to fix the dimension month... and to only update the expression inside?

I don t know if it s understanding

thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 19:05:55 GMT

Try:

Sum({$<Month=, Date={">=$(=MonthStart(AddMonths(MaxDate),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

That will disregard month selection for that expression only.

Re: Chart on the last 12 months

Fri, 14 Jun 2013 19:31:23 GMT

thank it s working for the month but i don t know why the chart doesn 't display the month of the previuos year

And it s strange cause if i select year= 2013 and month =april for example it stop at januray of 2013

i don t have the month of the 2012 ....

Have you any idea what is the origin of this problem ?

Tank a lot

Re: Chart on the last 12 months

Fri, 14 Jun 2013 19:35:17 GMT

I think there was an error in the formula. Try:

Sum({$<Month=, Year=, Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} [NbCustomer])

Re: Chart on the last 12 months

Fri, 14 Jun 2013 20:04:39 GMT

thank a lot , it s working if i want put an hierarchical dimension (month, week ) you think it s possible or i can t in the chart cause if i select an month my hierarchical go to the week and do you know if i can sort an hierarchical dimension and how ?

i ve a lot of question i know i m not an expert but thank a lot for your help

Re: Chart on the last 12 months

Fri, 14 Jun 2013 20:33:23 GMT

I don't think there is a problem about using a hierarchical dimension, but you must realize that if for example, you have a Month - Week dimension, when you select a Month, the expresion will take the max date for the selected month and the dates interval will change and show all the weeks that exist in the new set of dates according to your set analysis expression.

Also you'll have to check if you want to disregard the Week field selections, since if you select a Week the current set analysis won't disregard this selection and will only show information for that specific week. In order to disregard Week field selections you must add "Week=" to the expression.

Re: Chart on the last 12 months

Fri, 14 Jun 2013 20:55:16 GMT

yes ok thank i understand what you re saying but i would like to know if it s possible to when i select a month not on the chart but by using an list to keep the analyse on the month.

i would like to know if it s possible to change the analyse (dimension month to weeks) only by using the chart.

Maybe not...

Maybe i ve to use an dimesion circular and choose when i want to change the dimension ?

thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 20:55:21 GMT

yes ok thank i understand what you re saying but i would like to know if it s possible to when i select a month not on the chart but by using an list to keep the analyse on the month.

i would like to know if it s possible to change the analyse (dimension month to weeks) only by using the chart.

Maybe not...

Maybe i ve to use an dimesion circular and choose when i want to change the dimension ?

thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 21:08:30 GMT

do you know why when i try to sort mys hierarchy dimension (Month,week) by an expression (Month Number) it s not working as same as i ve only as dimension Month ?

How could i resolve and have the same sort as i ve one and the first dimension?

thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 21:08:34 GMT

do you know why when i try to sort mys hierarchy dimension (Month,week) by an expression (Month Number) it s not working as same as i ve only as dimension Month ?

How could i resolve and have the same sort as i ve one and the first dimension?

thank

Re: Chart on the last 12 months

Fri, 14 Jun 2013 21:53:48 GMT

The most probable reason why Week Dimension is not sorted as the Month Dimension is because the Week is the Week of the year, which goes from 1 to 52, and your set analysis creates a rolling 12 months interval. So if for example you select March of 2013, your formula will go from March 13 to April 12, which in weeks should also consist of 52 weeks. However, since the Week dimension doesn't states which year the week belongs to, QlikView will order the weeks by the numeric value, although in this case that's not correct, since the week 52 belongs to 2012 and the weeks 1 to the last of March belong to 2013.

Haha, that's when you have to analyze what type of analysis capabilities you want to enable for your graph. In this case perhaps you may solve this by making your week field having to possible interpretations, like the Month fileld does. Let me explain a little bit more... When you use the Month() function in the script, it creates a field that has two posible values, a string value like 'Jan', 'Feb', 'Mar'... and the corresponding numeric value for each string like 1, 2, 3... 12. So that's why you can disregard alphabetical order and sort the Month with the correct cronological order.

So, you can do the same to your Week field, although I recommend you create another field so you can use the normal Week field when you need it. Because you'll need the normal week field sooner or later.

In order to create a field with two possible values, numeric and string, you have to use the function called DUAL()

You would need to do something like the next in your script:

Dual ( Week (Date), WeekStart(Date) ) as Week_Year

You can check the parameters and rules about this function in the Help, but it's quite easy to use. Once you have your new Week_Year field, you can this as Dimensions and set the sort by Numeric Value and it should be ok now.

Regards.

Re: Chart on the last 12 months

Anonymous — Thu, 09 Oct 2014 10:00:14 GMT

HI carlos,

i need last 12 months remaining due for trx date can you please write the syntax

vtrx date is nothing but today date i need to to get last 12 months remaining due can u please write the syntax.

=rangesum(above(Sum(If(TRXDATE>addmonths(vTrxDate,-12) and DATE>vTrxDate and REMAININGDUE)),0,RowNo()))/100000

Thanks,

krishna

Re: Chart on the last 12 months

Wed, 06 Apr 2016 20:51:50 GMT

Hi Carlos,

Thanks for the solution. I am having one issue. I have a Combo chart and it show last 12 months. However, when I click on May 2015 it shows results until May 2014 because of the -12 month condition.

What I want to see: When the application is opened it will show the last 12 months but when user selects a specific month by clicking on the Bar. It should only show the results for that month only. Is this possible?

Thanks,

Darshan