Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

kicchu465
Contributor

Simple YTD

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

1 Solution

Accepted Solutions
HirisH_V7
Honored Contributor

Re: Simple YTD

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

HirisH
“Aspire to Inspire before we Expire!”
7 Replies

Re: Simple YTD

check below thread.

Set Analysis for certain Point in Time

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
kicchu465
Contributor

Re: Simple YTD

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

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..

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kicchu465
Contributor

Re: Simple YTD

Hi,

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

how to achieve with this data

prma7799
Honored Contributor III

Re: Simple YTD

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)

HirisH_V7
Honored Contributor

Re: Simple YTD

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

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
sumanta12
Contributor II

Re: Simple YTD

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)