Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

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

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
PrashantSangle

check below thread.

Set Analysis for certain Point in Time

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
kicchu465
Creator
Creator
Author

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. 

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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
Creator
Creator
Author

Hi,

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

how to achieve with this data

prma7799
Master III
Master III

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

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!”
sumanta12
Creator II
Creator II

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)