Qlik Community

New to Qlik Sense

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

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
dario_frimel
Contributor II
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
Specialist III
Specialist III

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

View solution in original post

9 Replies
Andrew_Incite
Partner - Contributor II
Partner - Contributor II

Try putting the Year set expression in quotes 

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

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

Channa
Specialist III
Specialist III

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

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
Specialist III
Specialist III

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

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
Specialist III
Specialist III

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

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

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

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