topic QTD in QlikView

QTD

Wed, 25 Mar 2015 14:36:09 GMT

Hi Experts,

I would like to know how Quarter Till Date(QTD) logic wil work .

Can any one explain with some sample data.

Thanks

Re: QTD

buzzy996 — Wed, 25 Mar 2015 14:40:30 GMT

Current Year QTD

=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

Previous Year QTD

=num(sum({$<Year = {$(=only(Year) - 1)}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

Re: QTD

chematos — Wed, 25 Mar 2015 14:47:27 GMT

Hi,

QTD is the cumulative result from 1st day of the quarter to the current day. Lets say you have sales amount everyday, e.g:

You want to calculate the QTD sales, being filtered 25/03/2015, so QTD should add sales amount from 1st January to 25/03/2015, as your current point of view is the first Quarter.

If your filter is for example 19/07/2014, then you are at Quarter 3, so QTD for Q1 is the cumulative sales amount of whole Q1, the same for Q2, and for Q3 should be from 1st of July till 19/07/2014

Hope this helps.

Regards,

Jose

Re: QTD

Wed, 25 Mar 2015 14:58:37 GMT

Thank you very nice explanation.

so how do we achieve this QTD, lets say I need to calculate the sales amount till today.

So how we can achieve,i think we need to use set analysis. How do we write set analysis function here???

Thanks

Re: QTD

buzzy996 — Wed, 25 Mar 2015 15:02:41 GMT

hi,are u tried the above expressions?

Re: QTD

chematos — Wed, 25 Mar 2015 15:06:49 GMT

Look Shiva expressions

Re: QTD

Thu, 26 Mar 2015 15:15:40 GMT

Thanku , I tried your expression but facing some error.

=Sum({$< Year = {$(#vYear)}, Quarter = {$(#vQuarter)} , Month = {<=$(#vMonth)}>} sales)

Any one correct me where i was wrong.

Re: QTD

buzzy996 — Fri, 27 Mar 2015 11:58:27 GMT

can u try this,else share ur error?

=num(sum({$<Year = {$(=only(vYear))}, Quarter = {$(=only(vQuarter))}, Month = {"<=$(=max(vMonth))"}>} sales), '$#,##0;($#,##0)')

Re: QTD

Anonymous — Fri, 27 Mar 2015 12:07:21 GMT

There is no need for set analysis here. Use function InQuarterToDate

Current quarter:

=sum(if(InQuarterToDate(Date, today(),0), sales))

Previous Quarter:

=sum(if(InQuarterToDate(Date, today(),-1), sales))

Previous Year same Quarter:

=sum(if(InQuarterToDate(Date, today(),-4), sales))

Re: QTD

Karim_Khan — Thu, 06 Aug 2015 09:10:59 GMT

Hi Shiva,

By Applying your expression its giving me the MTD data.Not QTD data.

Regards,

Re: QTD

Karim_Khan — Thu, 06 Aug 2015 09:13:12 GMT

Pls check my MTD expression

=round(sum({<FiscalYear={'$(vPriorYear)'},Month={'$(vMaxMonth)'}>}[Net Bookings]))

QTD expression

=sum({<FiscalYear = {'$(vCurrentYear)'}, Quarter = {'$(vQuarter)'},Month={'$(vMaxMonth)'},Date={'$(vMaxDate)'},Day={'<=$(vMaxDay)'},MonthYear=>} [Net Bookings])

Re: QTD

Karim_Khan — Thu, 06 Aug 2015 12:53:19 GMT

Pls check my MTD expression

=round(sum({<FiscalYear={'$(vPriorYear)'},Month={'$(vMaxMonth)'}>}[Net Bookings]))

QTD expression

=sum({<FiscalYear = {'$(vCurrentYear)'}, Quarter = {'$(vQuarter)'},Month={'$(vMaxMonth)'},Date={'$(vMaxDate)'},Day={'<=$(vMaxDay)'},MonthYear=>} [Net Bookings])