topic Re: YTD calulation in QlikView

YTD calulation

Karim_Khan — Mon, 17 Aug 2015 11:46:15 GMT

Hi Team,

I want YTD calculation on the basis of Monthly selection

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 11:48:41 GMT

You can... What is the format of your month?

Can you able to provide sample data or sample application?

Check enclosed sample file.

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 11:53:26 GMT

Cisco_Tool:

LOAD [End Customer Global Ultimate Name],

Country,

[End Customer HQ Top],

[Sales Level 1],

[Sales Level 2],

[Sales Level 3],

[Sales Level 4],

[Sales Level 5],

[Partner Name],

[Fiscal Period ID],

left([Fiscal Period ID],4) as Year,

Right([Fiscal Period ID],2) as Month,

FMV,

[Net Bookings],

Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Nikhil.G\CISCO\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

Fiscal_Calender:

LOAD [Fiscal Period ID],

[Fiscal Month],

[Calender Month],

Quarter

FROM

\\india.eclerx.com\ctrxdata\ARLM_R_DATA\Karim.Khan\Desktop\Fiscal_Calender.xlsx

(ooxml, embedded labels, table is Sheet1);

I am applying above script.

My expression for calcultaing YTD is as below

=sum({<Year={2015},Month={" >= $(vMinMonth) <= $(vMaxMonth)"},Month={'<=$(vFiscalNum)'}>}[Net Bookings])

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 11:55:28 GMT

Check my sample file enclosed in first reply.

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 12:00:03 GMT

In this I am able to show the YTD for selected month But I want sum of Month data.

In your Sample file.

Jan -150

Feb -250

Mar -350

But I want sum of Month e.g

If Feb will select then it should show 400 instead of 250

and Mar will select then it should show 750 instead of 350

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 12:01:59 GMT

Change Expression as below

=SUM({<Month = , Year =, Date = {">=$(=YearStart(MonthEnd(MakeDate(Max(Year),Max(Month)))))<=$(=MonthEnd(MakeDate(Max(Year),Max(Month))))"}>}Sales)

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 12:04:16 GMT

But Sir I don't have Date Field

Re: YTD calulation

qlikviewwizard — Mon, 17 Aug 2015 12:09:33 GMT

Without date field how can you achieve YTD? It is not possible.

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 12:12:21 GMT

It is Possible without date also.In one of my earlier qvd its working fine but other calculation is getting impacted so I am creating the new one which will show entire calculation correctly

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 12:28:01 GMT

Check enclosed file...

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 12:44:45 GMT

No Sir,Its not working I have the Fiscal Period ID only

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 12:50:16 GMT

Provide your sample qvw please

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 12:58:56 GMT

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 13:04:34 GMT

While Loading data in script define FiscalPeriodID as below

Date#(FiscalPeriodID,'YYYYMM') as FiscalPeriodID

Now use the same which I have provided...

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 13:07:43 GMT

But I already have field with the same name won't it give an error

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 13:09:48 GMT

FiscalPeriodID need to be used only once, so use only this...

Date#(FiscalPeriodID,'YYYYMM') as FiscalPeriodID

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 13:15:12 GMT

Re: YTD calulation

MK_QSL — Mon, 17 Aug 2015 13:19:20 GMT

It should be

[Fiscal Period ID]

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 13:21:03 GMT

sorry

Re: YTD calulation

Karim_Khan — Mon, 17 Aug 2015 13:26:30 GMT