topic Re: Calculate YTD for Month using Date in QlikView

Calculate YTD for Month using Date

pooja821 — Mon, 28 May 2018 06:29:14 GMT

Hi,

I need to calculate YTD for month using Date field.

For example for month of October-17, i need to calculate YTD for October, i.e. all month from Oct-16 to Oct-17 upon selection of Oct date from date filter.

Kindly help on this.

Re: Calculate YTD for Month using Date

pradosh_thakur — Mon, 28 May 2018 06:47:34 GMT

check this document out. Hope this helps.

YTQ, QTD, MTD and WTD

Re: Calculate YTD for Month using Date

pooja821 — Mon, 28 May 2018 07:04:33 GMT

I can fetch the answer from the above document.

They have converted the date field into numbers first using floor function.

I want to implement it using date field only.

Kindly help on this.

Re: Calculate YTD for Month using Date

sunny_talwar — Mon, 28 May 2018 13:06:54 GMT

What is your date field format? What object are you doing this in? Does it have Year, MonthYear as dimension?

Re: Calculate YTD for Month using Date

pooja821 — Tue, 29 May 2018 04:19:29 GMT

My Date is in DD-MMM-YYYY format.

i need to calculate this YTD for a specific month which is basically a previous month of selected date from date filter.

I m showing this in a graph (as below ) which basically have a month as dimension fetched from date only.

i need to calculate YTD for Dec-17 which basically is 4th previous month of Apr-18 as shown above in the graph.

I just have a date field.

Kindly help on this.

Re: Calculate YTD for Month using Date

shiveshsingh — Tue, 29 May 2018 04:46:24 GMT

if you select April, you need data from dec17 to Mar 18? Do you have Month Year field too in your model?

Re: Calculate YTD for Month using Date

pooja821 — Tue, 29 May 2018 05:15:08 GMT

No,

I need a YTD for Dec month i.e

Since the recent month is coming out be April-18 and all other previous months are reflecting accordingly.

what i want is that for Dec-17 month, i need to calculate YTD for this month i.e for Dec YTD the month should come out to be from April-17 to Dec-17 as our financial year starts from April of every year.

Re: Calculate YTD for Month using Date

shiveshsingh — Tue, 29 May 2018 05:22:28 GMT

So you mean to say, basis on your selection, result should be of previous month till Dec 17?

Re: Calculate YTD for Month using Date

pooja821 — Wed, 30 May 2018 05:15:37 GMT

Hi Sunny,

I have calculated YTD for month and stored in variable called $(Month_YTD) using below expression:

Concat(distinct {1<fiscal_year={"$(=$(year))"},

accounting_period={"$(='>=' & 1 & '<=' & $(v_accounting_period))"}>}Date,',')

Where $(year) is as concat(distinct {$<Date={"$(=$(prev_month4))"}>}fiscal_year,',')

and $(v_accounting_period) is as concat(distinct {$<Date={"$(=$(prev_month4))"}>} accounting_period,',')

The value of this Month_YTD variable is coming out to be 30-Nov-2017,31-Dec-2017 which is correct.

But when i m using this variable $(Month_YTD) in the below expression i.e.

count({$<Date={"$(=$(Month_YTD))"}>} distinct emp_id) , i am not getting the answers.

Although when i m manually feeding the dates in place of this Variable i.e

count({$<Date={'30-Nov-2017','31-Dec-2017'}>} distinct emp_id) ,then i m getting the answer .

Is there something wrong with this expression.

Kindly help on this.

Re: Calculate YTD for Month using Date

sunny_talwar — Wed, 30 May 2018 09:00:14 GMT

May be try this

Change Month_YTD to this

Chr(39) & Concat(DISTINCT {1<fiscal_year = {"$(=$(year))"}, accounting_period = {"$(='>=' & 1 & '<=' & $(v_accounting_period))"}>} Date, Chr(39) & ',' & Chr(39)) & Chr(39)

and then the main expression to this

Count({$<Date = {$(=$(Month_YTD))}>} DISTINCT emp_id)

Count({$<Date = {$(Month_YTD)}>} DISTINCT emp_id)