topic Re: Year To Month in QlikView

Year To Month

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have to show data by YTD (YTM) - no date selection (not required).

- Date filed is YearMonth (like 201705).

- Derived Year and Month from YearMonth field.

For instance :

Selections: Year = 2017 (2015,2016,2017) and Month = May (Jan....Dec), whenever i select (YTD) button report should display Year to Month data like 201701, 201702, 201703,201704,201705

Thanks,

Nihhal.

Re: Year To Month

Anonymous — Mon, 15 May 2017 12:57:11 GMT

Have a look: Set Analysis for certain Point in Time

Hope it helps!!

Re: Year To Month

Anonymous — Mon, 15 May 2017 13:14:37 GMT

Try this one

vCurrent_Year:

Year={"$(=GetFieldSelections(Year))"}

vYTD_Date:

Date={"<=$(=$(vSelected_Date))"}

Exp:

=sum({<Month,$(=vCurrent_Year),$(=vYTD_Date)

>}Sales)

Re: Year To Month

Anonymous — Mon, 15 May 2017 13:15:41 GMT

vSelected_Date:

Date(MakeDate(GetFieldSelections(Year),GetFieldSelections(Month),if(IsNull(GetFieldSelections(Day)),max({<Month={'$(=GetFieldSelections(Month))'}>}Day),GetFieldSelections(Day))),'YYYY/MM/DD')

Re: Year To Month

sivarajs — Mon, 15 May 2017 13:47:44 GMT

Variable 1: Num(Year(yearstart(Today())))&num(month(YearStart(Today())),'00')

Variable 2: Num(Year(yearstart(Today())))&num(month(Today()),'00')

Sum({<YearMonth={">=$(Variable1)<=$(Variable2)"}>} sales)

Re: Year To Month

nihhalmca — Mon, 15 May 2017 13:58:27 GMT

Hi Sivaraj - Thanks for your help.

Just i have given one year (2017) as example, actual scenario they are couple of years,

I think i have to created 2 more variables like vSelectedYear and vSelectedMonth.

am i right?

Re: Year To Month

Mon, 15 May 2017 14:06:36 GMT

Follow following steps:-

1) Create variable e.g:- vConvertDate, in definition, let it be empty

2) create button, in action tab, click on add, then select 'Set Variable'. In variable box input 'vConvertDate' and in value input 'CD' then finish/ok.

3) either create the desired format in script or in calculated dimension if you want, like ''Year(DateField)&Month(DateField)''.

4)tick the enable condition and write the following condition "if(vConvertDate='CD',1,DateField)"

Hope this will solve you problem. If not let us know, and if possible show some sample data.

Thanks,

Re: Year To Month

Anonymous — Mon, 15 May 2017 14:39:12 GMT

Try which i post , it will work

as i tried in my app already, hence it is working

Re: Year To Month

nihhalmca — Tue, 16 May 2017 05:57:57 GMT

Hi,

Thanks for your post. Actually do not have any day field in my requirement.

anyways can you share the app which you have worked out.

Re: Year To Month

Anonymous — Tue, 16 May 2017 07:32:11 GMT

Hi,

Just try to change the month in the app so that chart will visible as per you requirement

as per your requirement we supposed to maintain day field because it will give you all days in a month

example : if you select 2016, feb that means the should populate from jan 1 st to feb 28th , that's why i kept Day field

it's not a big deal to create a day field by using date right?

Regards,

Dev

Re: Year To Month

nihhalmca — Tue, 16 May 2017 07:47:36 GMT

Hi,

We are doing the month level analysis itself like Year to Month.

Moreover data model did not have date field (client do not want as well)

I hope you can understand.

Re: Year To Month

Anonymous — Tue, 16 May 2017 07:53:17 GMT

can you send sample app, so that it can be easy to resolve the issue

Re: Year To Month

nihhalmca — Tue, 16 May 2017 14:10:42 GMT

I tried below expressions even i did not get expected results

Ex1: Sum({<Year=,Month=, YearMonth = {">=$(vStartYTD)<=$(vEndYTD)"}>} Revenue)

Ex2: Sum({<Year=,Month=, YearMonth = {">=$(=vStartYTD)<=$(=vEndYTD)"}>} Revenue)

Ex3: Sum({<Year=,Month=, YearMonth = {">=$("vStartYTD")<=$("vEndYTD")"}>} Revenue)

vStartYTD = GetFieldSelections(Year)&num(month(YearStart(Today())),'00')

vEndYTD = GetFieldSelections(Year)&num(Month,'00')