Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
dario_frimel
New Contributor II

YTD and Previous YTD

Hi everyone,

I have read topics about this as it has been asked numerous times but I am not able to make it work for some reason on my own app. 

I have invoice table with invoice date and I have for same table seperate columns lets call them Year, Month and Day that pulls information from invoice date (it's predefind in sql year(invoicedate), month(invoicedate) and day(invoicedate) so I don't have to bother in Qlik doing it. 

So far I got with formula up to this, where I filtered subject, but when it comes to date I do not know how to approach as I tried various combinations and it didn't work, this allows me to filter current year and previous year but not up to date.

sum({<[invoicesheadings.SUBJECT]-={2324},[invoices.Year]={$(=Max([fakture.YEAR]))}>}[invoices.AMOUNT])

sum({<[invoicesheadings.SUBJECT]-={2324},[invoices.Year]={$(=Max([fakture.YEAR])-1)}>}[invoices.AMOUNT])

Any help is appreciated, thanks in advance.

 

 

Labels (2)
1 Solution

Accepted Solutions
Channa
Contributor III

Re: YTD and Previous YTD

Current Year YTD

=Count({$<AttendDate = {'>=$(=YearStart(Today()))<=$(=(Today()))'}>}EMP_ID)

For Previous YTD

=SUM({$<AttendDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( (Today()),-12))'}>}EMP_ID)

 

this will do current year till todate and last year till today

Channa
9 Replies
Andrew_Incite
New Contributor II

Re: YTD and Previous YTD

Try putting the Year set expression in quotes 

{"$(=Max([fakture.YEAR]))"}

{"$(=Max([fakture.YEAR])-1)"}

Channa
Contributor III

Re: YTD and Previous YTD

Hi you can't do YTD with year field you need date column

Current Year YTD

=Count({$<AttendDate = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}EMP_ID)

For Previous YTD

=SUM({$<AttendDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( MonthEnd (Today()),-12))'}>}EMP_ID)

Channa
dario_frimel
New Contributor II

Re: YTD and Previous YTD

Can I do this for days instead months since I want to see revenue on this day and in another KPI revenue on same day in previous year?
Channa
Contributor III

Re: YTD and Previous YTD

Current Year YTD

=Count({$<AttendDate = {'>=$(=YearStart(Today()))<=$(=(Today()))'}>}EMP_ID)

For Previous YTD

=SUM({$<AttendDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( (Today()),-12))'}>}EMP_ID)

 

this will do current year till todate and last year till today

Channa
dario_frimel
New Contributor II

Re: YTD and Previous YTD

Doesn't seem to work, I replaced AttendDate with [invoices.DATE] and EMP_ID with [invoices.Amount] but it doesn't read that line at all, it just sums up for all the years I have data. I tried without ' and with " and without either of those...
Channa
Contributor III

Re: YTD and Previous YTD

Check data format of your date column
Channa
dario_frimel
New Contributor II

Re: YTD and Previous YTD

It's DD/MM/YYYY hh:mm:ss AM/PM
dario_frimel
New Contributor II

Re: YTD and Previous YTD

Seems date format was issue, I used autoCalendar.Date and it works now with little modifications, thanks a lot!
Ash92
New Contributor II

Re: YTD and Previous YTD

@dario_frimel I'm stuck at the exactly the same point!! what exactly were your modifications to the date field?