topic Re: data must show with selection for MoM in QlikView

data must show with selection for MoM

prma7799 — Fri, 04 Dec 2015 07:24:30 GMT

Dear All,

I have one chart in that I wrote attached expression

So data is showing two year sales

My requirement is when user select financial year 2016-15

then data data will show for year 15 and 16

if user select financial year 2015-14

then data data will show for year 15 and 14 like this

(means current year and last year total 24 bars will show.)

Note: vFY=Year(addyears(max([Posting Date]),-4))

PFA..

Re: data must show with selection for MoM

tresB — Fri, 04 Dec 2015 07:37:00 GMT

Your date-fields-formats would matter while writing expression for the same. Try to create a sample qvw with representative data set where we can work upon to help you.

Re: data must show with selection for MoM

tresB — Fri, 04 Dec 2015 09:18:56 GMT

Your master table creation is too complex with unnecessary (and probably confusing too) fields. Try to cleanse it. FinancialYear field is not a year field a date. And that is one of the reasons, making your set comparison fail. It would take a long time to analyse your this app and suggest right expression, and I am a bit lazy. . And yes, overlapping date ranges in your set could be another reason of failure too.

Re: data must show with selection for MoM

prma7799 — Fri, 04 Dec 2015 09:22:42 GMT

Ok.

please give some alternate solution with existing master calendar script.

Thanks

Re: data must show with selection for MoM

tresB — Fri, 04 Dec 2015 09:33:51 GMT

There is no sales data for year 14,13 in your sample qvw.

Re: data must show with selection for MoM

Anonymous — Fri, 04 Dec 2015 09:49:12 GMT

Hi,

Please refer to the below link:

https://community.qlik.com/docs/DOC-9250

It will help.

Thanks.

Re: data must show with selection for MoM

Mark_Little — Fri, 04 Dec 2015 09:55:56 GMT

Hi,

The away i always approach a financial year like mentioned, is adding the information into your master calendar.

Then use that as a selector.

Mark

Re: data must show with selection for MoM

prma7799 — Fri, 04 Dec 2015 10:12:35 GMT

Hi Mark / tresesco‌

Please ignore the previous file it was not proper.

PFA updated file.

Thanks

Re: data must show with selection for MoM

tresB — Fri, 04 Dec 2015 10:15:48 GMT

Access denied !

Re: data must show with selection for MoM

prma7799 — Fri, 04 Dec 2015 11:19:52 GMT

PFA

Re: data must show with selection for MoM

tresB — Fri, 04 Dec 2015 11:26:30 GMT

Like this?

Re: data must show with selection for MoM

prma7799 — Fri, 04 Dec 2015 12:38:19 GMT

Hi tresesco‌,

Your solution is ok but I have some different requirement that

1.I want current month with previous 23 months data total 24 bars should display without any selection

2.with selection

if suppose user select any financial year like 14-15 then also 24 bar should display but here only 21 bar present

and for 13-14 the 2015 year data also come .

See attached.

Thanks in advance

Re: data must show with selection for MoM

Fri, 04 Dec 2015 13:27:06 GMT

I use set analysis to pick up last 24 month:

Sum({$<[Month]=, [Year]=, [Date]={">=$(=MonthStart(AddMonths(Max(Makedate(vYYYY, vMM)),-23)))<$(=MonthEnd(Max(Makedate(vYYYY, vMM))))"}>} AMT)

You may try it.

Re: data must show with selection for MoM

prma7799 — Mon, 07 Dec 2015 04:01:11 GMT

Hi tresesco‌,

Any help on this?

Re: data must show with selection for MoM

tresB — Mon, 07 Dec 2015 04:53:20 GMT

Your expected output is a fair ask, but with the calendar you created is not. If you could create a sample qvw with inline data, I could help.

Re: data must show with selection for MoM

Mon, 07 Dec 2015 15:53:33 GMT

Create a expression for list box such as '=(Year-1)&'-'&(Year)', and ignore other value you may add to app. Please see attached qvw.

Thanks.