topic Re: YTD in qlikview in QlikView

YTD in qlikview

psk180590 — Wed, 21 Jun 2017 12:17:40 GMT

Hello All,

I'm looking to generate a chart for YTD current year vs YTD last year. I have many values to calculate(Sales, Quantity, Per Country, Per State, Per Region etc.,). Each of this will be having a separate chart.

Can you please help me with a single variable to define YTD current year and another one to define YTD last year.

I would like to use this in the chart expressions.

Thanks,

Best Regards

Sai.

Re: YTD in qlikview

prma7799 — Wed, 21 Jun 2017 12:19:26 GMT

Please check below using this you can create variable and use it in you application

YTQ, QTD, MTD and WTD

Previous YTQ, QTD, MTD and WTD

Re: YTD in qlikview

PrashantSangle — Wed, 21 Jun 2017 12:27:04 GMT

create 2 variable

Considering you have proper date field

1 for YTD like

'=$(>=YearStart(max(datefieldName))<=(date(max(dateFieldName))))'

2 for LY-YTD like

'=$(>=YearStart(AddYears(max(datefieldName),-1))<=(date(AddYears(max(dateFieldName),-1))))'

Regards,

Re: YTD in qlikview

psk180590 — Wed, 21 Jun 2017 12:43:53 GMT

Hello Prashant,

I'm using the below formula. But, it doesn't give me any values

SET vDate_APO_YTD = =">$(=YearStart(max(APORegion.Date))<=(date(max(APORegion.Date))))}>";

SET vDate_APO_YTD_VJ = =">$(=YearStart(AddYears(max(APORegion.Date),-1))<=(date(AddYears(max(APORegion.Date),-1))))}>";

Could you please check if i'm missing anything here.

Re: YTD in qlikview

PrashantSangle — Wed, 21 Jun 2017 12:56:15 GMT

What is your expression in set analysis????

In chart you have to use those variable

Try like

sum({<dateField={"=$vDate_APO_YTD"}>}Sales)

and

sum({<dateField={"=$vDate_APO_YTD_VJ"}>}Sales)

Note : Please check syntax of expression. I am bad with syntax

Regards,

Prashant

Re: YTD in qlikview

psk180590 — Wed, 21 Jun 2017 15:32:05 GMT

Hello All,

For some reason i'm not able to get the desired result. I'm attaching my test data here for reference can you please have look and suggest me what i might be doing wrong.

Re: YTD in qlikview

psk180590 — Wed, 21 Jun 2017 15:36:38 GMT

I'm looking to get the Sales(2017 YTD) vs Sales(2016 YTD) and also Sales_Total in the same format.

Re: YTD in qlikview

psk180590 — Wed, 21 Jun 2017 15:39:23 GMT

jagan‌

I have seen your post regarding the same topic but, some how i'm not able to follow your suggestions. Can you please help me on this.

Re: YTD in qlikview

Anonymous — Wed, 21 Jun 2017 16:30:57 GMT

YTD:

=sum({<Date={">=$(=Yearstart(today()))<=$(today())"} >} Sales)

Y-1TD-1:

sum({<Date={">=$(=Yearstart(today(),-1))<=$(=AddYears(today(),-1))"} >} Sales)

Re: YTD in qlikview

psk180590 — Thu, 22 Jun 2017 07:17:38 GMT

Hello Robin,

Thanks very much for your response.

The YTD (current year) formula works perfectly but the YTD (last year) gives me same values as YTD current year.

Re: YTD in qlikview

mohammadkhatimi — Thu, 22 Jun 2017 09:45:39 GMT

YTD Flag: if(if(Month(date(Floor(Date)))<=3,(Year(date(Floor(Date)))-1)&'-'&(Year(date(Floor(Date)))),(Year(date(Floor(Date))))&'-'&(Year(date(Floor(Date)))+1))=if(Month(((today())))<=3,(Year(((today())))-1)&'-'&(Year(((today())))),(Year(((today()))))&'-'&(Year(((today())))+1)),1,0) as YTD_Flag,

LYTD Flag: inyeartodate(Date, $(vDateToday), -1,4)* -1 AS LastYTDFlag

Try this in script make flag.In front end write expression as :

YTD =sum({<YTD={'1'}>} Sales)

LYTD =sum({<LYTD={'1'}>} Sales)

Hope this will helps u..!!

Regards,

Mohammad

Re: YTD in qlikview

Anonymous — Thu, 22 Jun 2017 10:17:08 GMT

It tried it with your example in a textbox, it worked...

Re: YTD in qlikview

Kushal_Chawda — Thu, 22 Jun 2017 10:22:53 GMT

try this

YTD:

=sum({<Date={">=$(=Yearstart(today()))<=$(today())"} >} Sales)

Y-1TD-1:

sum({<Date={">=$(=Yearstart(AddYears(today(),-1)))<=$(=AddYears(today(),-1))"} >} Sales)

Re: YTD in qlikview

psk180590 — Thu, 22 Jun 2017 13:34:19 GMT

Hello All,

Firstly thanks for all your suggestions.

This is turning out to be bit more complicated then i thought it would. So, here is what i'm looking to do.

Get YTD current year vales for each month.

1st-31st Jan is YTD Jan
1st Jan to 28th Feb is YTD Feb
1st Jan to 31st Mar is YTD Mar
1st Jan to 30th Apr is YTD Apr
And so on for the preceding months.

I was able to achieve the YTD for my data upto May 31st(i.e., 1st Jan to 31st May) but it works only without the Month dimension in my chart.

Now, what i would like to have is to generate a value for each point mentioned above and have them with a month dimension.

NOTE: I believe that having date formulas would be better as i have many expressions(Sales and Quantity of three different types). I mean having a Date variable that would get me the Period from 1st Jan to 31st Jan and the next one to get Period from 1st Jan to 28th Feb and so on.

Re: YTD in qlikview

Anonymous — Thu, 22 Jun 2017 15:16:26 GMT

when you have multiple years why do use use month instead of yearmonth as dimension?

basically it should work with something like rangesum(above(sum(Sales),0,RowNo())

if YearMonth is the only dimension and YearMonth is sorted ascending.