Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
TrudyD1474
Contributor
Contributor

Select Transactions Between Two Dates

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

4 Replies
sonkumamon
Creator
Creator

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

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

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])