topic Re: Dynamic chart comparing previous year month with current year month in QlikView

Dynamic chart comparing previous year month with current year month

ankurakash — Mon, 18 Jul 2016 07:17:08 GMT

Dear Community Members,

I am trying to figure out solution for a dynamic chart that compare previous year month with current year month and as the counting of current year month grows it automatically showcase the compariosn with respective month of last year. Otherwise it should only show the compariosn.

For example:

Current Year = 2016; Current available Month data = Jan to July

previous Year = 2015; Previous available Month data = Jan to Dec

The chart (could be bar chart) should display month compariosn between two years for available Months (Jan to July). It should not display previous year months (Aug to Dec) which don't have compariosn months in the current year.

Dynamic behavior required:

If the counting of current year month grows(i.e. Aug and Sep 2016) the chart should include them dynamically and compare it with the respective month of previous year.

A solution or a guiding approach is much appreciated.

Regards,

Ankur

Re: Dynamic chart comparing previous year month with current year month

kaushiknsolanki — Mon, 18 Jul 2016 08:12:28 GMT

Hi,

Look at the link below.

Set Analysis for certain Point in Time

Regards,

Kaushik Solanki

Re: Dynamic chart comparing previous year month with current year month

ankurakash — Mon, 18 Jul 2016 09:17:32 GMT

Hi Kaushik,

Thanks for sharing the link.

I was trying the following query:

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today()))<=$(=Today())’}>} Sales)

Would it be possible for you to share an example for the shared query.

Regards,

Akash

Re: Dynamic chart comparing previous year month with current year month

effinty2112 — Mon, 18 Jul 2016 10:45:35 GMT

Hi Ankur,

Lines something like this in your script:

LET vToday = Today();

if(DayNumberOfYear(Date)<=DayNumberOfYear($(vToday)),1,0) as YTD;

would work well in a date dimension table such as a master calender. Then if you make a year to date comparison you can select YTD = 1 in a listbox or use the YTD field in a set modifier such as <YTD = {1}>

Cheers

Andrew

Re: Dynamic chart comparing previous year month with current year month

Mon, 18 Jul 2016 13:16:21 GMT

Hope this helps.

Re: Dynamic chart comparing previous year month with current year month

ankurakash — Mon, 18 Jul 2016 15:50:51 GMT

Hi Saugat,

I don't have license edition at present. Can you share the same in a text file to me. Appreciate your help.

Regards,

Ankur Akash

Re: Dynamic chart comparing previous year month with current year month

ankurakash — Mon, 18 Jul 2016 15:57:51 GMT

Hi Andrew,

I presume this will give me comparison between date. If the solution is otherwise, I would request more details.

Re: Dynamic chart comparing previous year month with current year month

Tue, 19 Jul 2016 08:51:07 GMT

Hi Ankur,

In the setting-->variable overview create the variable:

vvmaxyear=max(year)

vvmaxmonth=max({<year={$(vvmaxyear)}>}month)

Now,taking year and month as dimensions and in the expressions tab, try this expression.

=sum({<year={"<=$(vvmaxyear) >=$(=$(vvmaxyear)-1)"},month={"=$(=vvmaxmonth)"}>}sales)

Thanks

Re: Dynamic chart comparing previous year month with current year month

ankurakash — Tue, 19 Jul 2016 11:22:24 GMT

Hi Saugat,

This works exactly as per my requirement. Thanks for penning the steps and the expressions.

Regards,

Ankur