topic Re: YTD calculations in QlikView

YTD calculations

suryaa30 — Wed, 27 Sep 2017 13:07:32 GMT

Hi All I am expecting a chart like below.

Dimension: Year

Expression: Count(GrossSales_Policy)

Now I want to create a YTD chart. Here YTD means the maximumum date in the maximum year.

so max year is 2017

max date is 27

max month is sep

in the chart i should see data for

1-jan-2016 to 27-Sep-2016

1-jan-2015 to 27-Sep-2015

Individually I have calculated them using below expressions. I want to have a expression to accomodate any number of year's data. so I need not add expression for each year.

if 2016 is selected then for CY=2016

PY=2015

PY_1=2014

eYTDMovementsCY

Count({<Year={'$(=Max(Year))'},Date3={"<=$(=$(vSelectYrDate))"},Month=>}GrossSales_Policy)

eYTDMovementsPY

Count({<Year={'$(=Max(Year)-1)'},Date3={"<=$(=$(vPrevYrDate))"},Month=>}GrossSales_Policy)

vMaxYear Year(Max(Date3))

vMaxPrYear Year(Max(Date3))-1

vAllMaxMonth Num(Month(Max( ALL Date3)))

vAllMaxDate Date(Max(ALL Date3), 'DD-MMM-YY')

vAllMaxDay Day(Max(ALL Date3))

vSelectYrDate Date(MakeDate($(vMaxYear),$(vAllMaxMonth),$(vAllMaxDay)), 'DD-MMM-YY')

vPrevYrDate Date(MakeDate($(vMaxPrYear),$(vAllMaxMonth),$(vAllMaxDay)), 'DD-MMM-YY')

master calendar used;

Re: YTD calculations

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:10:30 GMT

You can use YearStart() in set analysis

Re: YTD calculations

suryaa30 — Wed, 27 Sep 2017 13:12:25 GMT

No I don't understand. Can you please elaborate?

Re: YTD calculations

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:15:28 GMT

This is my intention

Count({<Year={'$(=Max(Year))'},Date3={">=$(=Date(YearStart(Max($(vSelectYrDate))))) <=$(=$(vSelectYrDate))"},Month=>}GrossSales_Policy)

Re: YTD calculations

suryaa30 — Wed, 27 Sep 2017 13:15:33 GMT

The Idea is right now I have

Dimension: Year

Expression: Count(Policy)

For YTD chart I need same as above a single expression chart

Dimension: Year

Expression: Count({Set expression} Policy)

Entire data set Max date 27-Sep

the set expression should restrict data from starting of any year till 27-Sep of that year

Re: YTD calculations

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:17:28 GMT

For YTD chart I need same as above a single expression chart

Dimension: Year

Expression: Count({Set expression} Policy)

Entire data set Max date 27-Sep

the set expression should restrict data from starting of any year till 27-Sep of that year

Yes, This is my answer

Count({<DateField = {">=$(=Date(YearStart(Max(DateField)))) <=$(=Max(DateField))"}>} Policy)

Re: YTD calculations

suryaa30 — Wed, 27 Sep 2017 13:34:48 GMT

Just tell me something

what should be the format for Date Field in your expression?

Re: YTD calculations

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:37:25 GMT

Try from your end. My format is DD-MM-YYYY

Count({<DateField = {">=$(=Date(Date#(YearStart(Max(DateField)),'YourFormat'),'DateFieldFormat')) <=$(=Max(DateField))"}>} Policy)

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 14:02:39 GMT

stalwar1‌ loveisfail‌ kush141087‌

Need your help.

Hi In attached App we have sheets A and B

Both Sheets have Monthly and YTD charts.

In sheet A YTD chart I have used 3 expression to achieve my need which is For each year the data should be between start of that year to Max date determined.

I want this to be done in a single expression so that as the number of years increase I dont have to write a separate expression for each year.

I need a set expression similar to this

max data in entire data model. 2-Jun

Dim: Year and Month

Exp:Sum({for all data between start of the year and 2-Jun of that year}Revenue)

In YTD the values for each month should be accumulative.

Actual:

2011

Jan-10

Feb-20

Mar-30

YTD:

2011

Jan-10

Feb-30(10+20)

Mar-40((10+20+30)

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 14:10:41 GMT

May be try this

Sum({<[Trade Date] = {"=SetDateYear([Trade Date], Max(TOTAL Year)) <= Max(TOTAL [Trade Date])"}>} [Trade Revenue])

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 14:23:12 GMT

Forgot to mention that In YTD the valyues for each month should be accumulative.

Actual:

2011

Jan-10

Feb-20

Mar-30

YTD:

2011

Jan-10

Feb-30(10+20)

Mar-40((10+20+30)

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 14:31:49 GMT

This?

Aggr(RangeSum(Above(Sum({<[Trade Date] = {"=SetDateYear([Trade Date], Max(TOTAL Year)) <= Max(TOTAL [Trade Date])"}>} [Trade Revenue]), 0, RowNo())), Year, Month)

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 14:47:32 GMT

The values dont add up properly,

for convenience I have used sum([Trade Revenue])/100000000

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 15:00:17 GMT

Use this if you have QV12 or above

Aggr(RangeSum(Above(Sum({<[Trade Date] = {"=SetDateYear([Trade Date], Max(TOTAL Year)) <= Max(TOTAL [Trade Date])"}>} [Trade Revenue])/100000000, 0, RowNo())), Year, (Month,(NUMERIC)))

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 15:02:02 GMT

My Bad it is 11.20.... So any work around? should I settle for Individual values?

thank You very Much

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 15:06:56 GMT

The problem is the your Month field is not sorted in the correct order... Seems like you have a master calendar setup in your app but it is getting loaded from a qvw... if you can sort the data in your qvw... it will work...

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 15:08:48 GMT

Alternatively, if you can keep Year as your first dimension and Month as second dimension... this can work (without aggr)

RangeSum(Above(Sum({<[Trade Date] = {"=SetDateYear([Trade Date], Max(TOTAL Year)) <= Max(TOTAL [Trade Date])"}>} [Trade Revenue])/100000000, 0, RowNo()))

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 15:10:22 GMT

So I should be sorting my entire Fact Table on Trade Date in the script? Although this is just a sample data let me give it a try.

Thank you so much

Re: YTD calculations

sunny_talwar — Thu, 28 Sep 2017 15:12:25 GMT

Not the trade date.... but if you can sort your master calendar, then that will do it....

Re: YTD calculations

suryaa30 — Thu, 28 Sep 2017 15:22:02 GMT

Appreciate your Time Thanks Much