Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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!”

View solution in original post

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)
Highlighted
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!”

View solution in original post

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)