topic Re: Simple YTD in QlikView

Simple YTD

kicchu465 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Can anyone help me out to show the YTD sales with sample data with 10-15 records?

how to achieve this? can anyone give sample script and expressions to get.

Am new to Qlik

thanks

Re: Simple YTD

PrashantSangle — Thu, 16 Nov 2017 07:03:23 GMT

check below thread.

Set Analysis for certain Point in Time

Regards,

Re: Simple YTD

kicchu465 — Thu, 16 Nov 2017 07:05:16 GMT

thanks prashanth,

I checked all the threads but am not usnderstand the things ,

that's why I asked with sample 10 records data to achieve this.

Re: Simple YTD

Anil_Babu_Samineni — Thu, 16 Nov 2017 07:06:45 GMT

This?

YTQ, QTD, MTD and WTD

Previous YTQ, QTD, MTD and WTD

Example - also there in first link and you can read requirement of text..

Re: Simple YTD

kicchu465 — Thu, 16 Nov 2017 07:08:23 GMT

Hi,

here is the sample I need YTD for this year and last year aswell,

how to achieve with this data

Re: Simple YTD

prma7799 — Thu, 16 Nov 2017 07:26:38 GMT

Try like this

YTD

sum({<Date = {">$=(=YearStart(Max(Date),0,4))<=$(=Max(Date))"}>}Sales)

LYTD

=Sum({<Year=, Month=, Date={">=$(=Num(YearStart(Max(Date),-1)))<=$(=AddYears(Max(Date), -1))"}>} Sales)

Re: Simple YTD

HirisH_V7 — Thu, 16 Nov 2017 10:41:25 GMT

Hi Check this,

By using the sample data, i have created a YTD Flag in script ,

Data:
 LOAD *,
 year(Date) as Year,
 Num(Month(Date)) as MonthNum,
 Month(Date) as Month
 INLINE [
 
 Date, Value
 1/01/2016, 
 1/02/2016, 6
 1/03/2016, 12
 1/04/2016, 18
 1/05/2016, 24
 1/06/2016, 30
 1/07/2016, 36
 1/08/2016, 42
 1/09/2016, 48
 1/10/2016, 54
 1/11/2016, 60
 1/12/2016, 66
 1/01/2017, 72
 1/02/2017, 78
 1/03/2017, 84
 1/04/2017, 90
 1/05/2017, 96
 ]; 

//Generating YTD in script
 AsOf:
 LOAD DISTINCT Date as AsOfDate
 RESIDENT Data
 ;
 LEFT JOIN (AsOf)
 LOAD AsOfDate as Date //Renaming the field
 RESIDENT AsOf
 ;
 INNER JOIN (AsOf)
 LOAD *,1 as YTD
 RESIDENT AsOf
 WHERE year(AsOfDate) = year(Date)
 AND AsOfDate >= Date
 ;

You can use that flag in set-analyis with Max(Year) & you can display the data.

Sum({<YTD={1},Year={'$(=Max(Year))'}>}Value)

or else, by direct passing the dates into set analysis,

Sum({<Date={">=$(=YearStart(Max(Date)))<=$(=Date(Max(Date)))"}>}Value)

Hope this helps,

PFA

Hirish

Re: Simple YTD

sumanta12 — Thu, 16 Nov 2017 10:51:52 GMT

Hi,

First Load the data:

TABLE:

LOAD

Product,

Year,

Month,

Date,

Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Then Declare variable in Variable overview:

vMaxDate=Max(Date)

vMaxYear=Year(Max(Date))

vLastYear=Year(Max(Date))-1

vLYEndDate=Date(YearStart(max(Date))-1)

Then write in expression for This year:

SUM({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'}>}Sales)

Then write in expression for Last year:

SUM({<Year={$(vLastYear)},Date={'<=$(vLYEndDate)'}>}Sales)