Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

YTD and Previous YTD

Hi,

I'm trying to figure out a simple YTD and previous YTD expression:

1.  The year follows the calendar year, so I'm looking for 1/1/17 through 6/13/17 in comparison to 1/1/16 through 6/13/16.

2.  The date I'm using is the Transactions.Txn Date

3.  I'm trying to find sales using the Transactions.Amount With Sign field

I've looked at other boards, but it's a little over my head.  Can anyone help?


Thanks-

Tags (1)
1 Solution

Accepted Solutions
galax_allu
Valued Contributor

Re: YTD and Previous YTD

For this  year :

1/1/17 through 6/13/17 in

=Sum({$ <Transactions.Txn Date={"$(='>=' & Date(YearStart(Max(Transactions.Txn Date)), 'MM/DD/YYYY') & '<=' & Date((Max(Transactions.Txn Date)), 'MM/DD/YYYY'))"}>} [Transactions.Amount])

for previous year

/1/16 through 6/13/16.

=Sum({$ <Transactions.Txn Date={"$(='>=' & Date(YearStart(Max(Transactions.Txn Date),-1), 'MM/DD/YYYY') & '<=' & Date(addyears(Max(Transactions.Txn Date),-1), 'MM/DD/YYYY'))"}>} [Transactions.Amount])

3 Replies
Partner
Partner

Re: YTD and Previous YTD

Nicole,

The most common way of dealing with that is by building a Master Calendar based on your data, in your case the Transactions.Txn Date field.  Part of that master calendar will be to determine what dates are considered YTD or PYTD.

Check out these posts for help:

The Master Calendar

Year-over-Year Comparisons

You can then use the fields from the master calendar in your expression.

Good Luck!

Oscar

galax_allu
Valued Contributor

Re: YTD and Previous YTD

For this  year :

1/1/17 through 6/13/17 in

=Sum({$ <Transactions.Txn Date={"$(='>=' & Date(YearStart(Max(Transactions.Txn Date)), 'MM/DD/YYYY') & '<=' & Date((Max(Transactions.Txn Date)), 'MM/DD/YYYY'))"}>} [Transactions.Amount])

for previous year

/1/16 through 6/13/16.

=Sum({$ <Transactions.Txn Date={"$(='>=' & Date(YearStart(Max(Transactions.Txn Date),-1), 'MM/DD/YYYY') & '<=' & Date(addyears(Max(Transactions.Txn Date),-1), 'MM/DD/YYYY'))"}>} [Transactions.Amount])

Not applicable

Re: YTD and Previous YTD

Thank you both, this worked!