topic Re: Variance for YTD in QlikView

Variance for YTD

gautik92 — Thu, 30 Oct 2014 09:27:07 GMT

How to calculate the variance of YTD as i have YTD(Current year) and YTD(Previous Year)

Re: Variance for YTD

Thu, 30 Oct 2014 09:36:09 GMT

Hi,

we have current year data we can cal it as YTD,previous year data we can cal it as a PYTD

Plz find the master calender link you vl get more information.

Creating A Master Calendar

Re: Variance for YTD

gautik92 — Thu, 30 Oct 2014 09:42:25 GMT

I Want to find variance that is GAP for Current and previous year YTD

Re: Variance for YTD

Kushal_Chawda — Thu, 30 Oct 2014 09:42:45 GMT

You can calculate as

(YTD- PYTD)/ PYTD

Re: Variance for YTD

gautik92 — Thu, 30 Oct 2014 09:57:19 GMT

Can we do it for Sum(sales)

i found YTD by using YTD==sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

LY YTD==sum({<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

now how can i calculate Variance

Re: Variance for YTD

Kushal_Chawda — Thu, 30 Oct 2014 10:01:56 GMT

(sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty) - sum({<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty ) /

sum({<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty )

Re: Variance for YTD

Thu, 30 Oct 2014 10:02:12 GMT

Variance:

sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>

<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

Variance Percentage:

sum({<Year={$(=Max(Year))},Transactiondate={"<=$(=max(Transactiondate))"}>

<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty)

Fabs(Sum({<Year={$(=Max(Year)-1)},Transactiondate={"<=$(=max(Transactiondate))"}>} Qty))

Re: Variance for YTD

Thu, 30 Oct 2014 10:06:44 GMT

As you can see the expression above contain differential analysis in the same Sum function.

That syntax is clean and powerful.

Re: Variance for YTD

gautik92 — Thu, 30 Oct 2014 10:13:01 GMT

Its is not working but thanks man

Re: Variance for YTD

Kushal_Chawda — Thu, 30 Oct 2014 10:20:36 GMT

Can you post some sample data?

Re: Variance for YTD

gautik92 — Thu, 30 Oct 2014 10:22:57 GMT

Thank you guys i got the answer

Re: Variance for YTD

shruthibk — Wed, 06 Jan 2016 15:43:24 GMT

hi how can i achieve variance YTD for latest four years

regards,

shruthi