Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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
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
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?
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])