Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I'm getting crazy because - I guess - a small problem:
I import Data from a SQL-Database with OLE-DB - there is a data field (Date) with the format DD.MM.YYYY hh:mm.
My settings look like that:
SET TimeFormat='hh:mm';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm';
In another field a got some Entries per Month (SU_TIME) over 3 years.
Now I just want to show the average per month with AVG() and select one year as following:
AVG ({$<Date={2015}>}SU_TIME)
Dimenson is just Date.
The diagramm won't show any data to me.
Even simple if-statements do not work with Date ...
Hopefully you can help me!
Thanks a lot,
Jan
If you create a field named Year you could do like this:
AVG ({$<Year={2015}>}SU_TIME)
But if you don't want to create a year field, you need to do something like this:
AVG({$<Date={">=01.01.2015<=31.12.2015"}>}Date)
If you create a field named Year you could do like this:
AVG ({$<Year={2015}>}SU_TIME)
But if you don't want to create a year field, you need to do something like this:
AVG({$<Date={">=01.01.2015<=31.12.2015"}>}Date)
a "dirty" solution could be perhaps:
AVG ({$<Date={"*2015*"}>}SU_TIME)
This expression should work
AVG ({$<Date={2015}>}SU_TIME)
But, I doubt whether Date has 2015 Date. May be change it to Year rather Date
Hi Henrik,
I'll try tomorrow morning.
But why does it help to create another field? I already have mit "Date" Field with 2015, 2016, 2017 inside. You mean that Qlikview links them afterwards automatically?
Thank you very much!
Ah - I see what you mean!
Like a string-search
But why cannot I compare it like I do?
Would be nice to habe something like If(Date>2015,..,...)
Thank you!!
Yes - I guess it should but it doesn't..
Date has for example: 01.05.2015 08:00
Thanks!
Your date field contains day, month and year. If you have a field that contains only year, you can do as you tried. Otherwise you'll have to do a search in the field using set expression, in the way I suggested or with the actually simpler solution "*2015*" that Robin suggested.
You can do something like:
if(Year(Date)>2015, 'yes', 'no')
That would print "yes" if the year is larger than 2015, i.e. 2016 and onwards.
Ahh - thank you very much!
I understood