Select Transactions Between Two Dates

[TrudyD1474]

I'm using Advanced Reports connected to Quickbooks Enterprise.

I'm only a week into using this reporting tool (which Qlik)

I'm trying to create a Profit & Loss report showing:

• Current Month
• Same Month Last Year
• Current YTD (Ending Date - Beginning Date)
• Last Year YTD (Last Year Ending Date - Last Year Beginning Date)

Basically I want to select all transactions between two dates.

Should be simple.  I know how to do it in Crystal Reports, Excel, Access.  But I don't understand the syntax in Qlik.  And, I can't find any vanilla examples. 

I need an example and an explanation of the example.

Thank you in addvance.

Trudy

[mayuringale25]

Hi @TrudyD1474 ,

Refer this links

https://community.qlik.com/t5/QlikView-Documents/Previous-YTQ-QTD-MTD-and-WTD/ta-p/1494128  

https://community.qlik.com/t5/QlikView-Documents/YTQ-QTD-MTD-and-WTD/ta-p/1489245/show-comments/true

[sonkumamon]

Current Month:

Sum({<MonthYear={$(=Max(MonthYear))}>} KPI)

Same Month Last Year:

Sum({<MonthYear={$(=AddYears(Max(MonthYear),-1))}>} KPI)

Current YTD:

Sum({<Date={">=[Beginning Date]<=[Ending Date]"}>} KPI)

Last Year YTD:

Sum({<Date={">=$(=AddYears([Beginning Date],-1))<=$(=AddYears([Ending Date],-1))"}>} KPI)

Where [MonthYear] field is a calculated dimension in the script using the following formula:

Date(Date#(Month(Date)&'-'&Year(Date),'MM-YYYY'),'MM-YYYY') as MonthYear

[TrudyD1474]

Ok, I got it to work.  Thank you.

Sum({$<[Transactions.Txn Date]={">=1/1/2019<=12/31/2019"}>}[Transactions.Amount With Sign])

But when I try to replace the hard coded dates with variables all I get is zeroes.

Sum({$<[Transactions.Txn Date]={">=[CurrMinDate]<=[CurrMaxDate]"}>}[Transactions.Amount With Sign])

CurrMinDate=01/01/2020     CurrMaxDate=12/31/2020.

I don't have anything selected except YEAR= 2019 and 2020

What am I doing wrong?

[sonkumamon]

Hi, in order to make the variable work, you need to 'activate' them with a $ sign.

Try the following:

Sum({$<[Transactions.Txn Date]={">=$(CurrMinDate)<=$(CurrMaxDate)"}>}[Transactions.Amount With Sign])