Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

ankurakash
New Contributor III

Dynamic chart comparing previous year month with current year month

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

1 Solution

Accepted Solutions
Not applicable

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

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

8 Replies

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

Hi,

Look at the link below.

Set Analysis for certain Point in Time

Regards,

Kaushik Solanki

ankurakash
New Contributor III

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

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

effinty2112
Honored Contributor

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

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

Not applicable

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

Hope this helps.

ankurakash
New Contributor III

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

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

ankurakash
New Contributor III

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

Hi Andrew,

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

Not applicable

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

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

ankurakash
New Contributor III

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

Hi Saugat,

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

Regards,

Ankur

Community Browser