Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table like this:
ID Date-Time Value
1 12/10/2010 08:00:00 AM 10
1 12/10/2010 08:00:00 PM 20
2 12/10/2010 08:00:00 AM 50
2 12/10/2010 08:00:00 PM 40
1 12/11/2010 08:00:00 AM 20
1 12/11/2010 08:00:00 PM 10
2 12/11/2010 08:00:00 AM 40
2 12/11/2010 08:00:00 PM 60
Now I want to display a straight table that shows avg(value) per id per day
Result:
ID Date Value
1 12/10/2010 15
2 12/10/2010 45
1 12/11/2010 15
2 12/11/2010 50
Does any one know how to do this in qlikview (in the expressions/ in the script...anything is fine)?
Thanks in advance
You can do it completely in the application (no script work). Start with ID as a dimension and then create another dimension: Date([Date-Time]). Then create an expression: Avg(Value).
EDIT: I ended up using Date([Date-Time]) in the load and then using Date#([Date-Time]) as the calculated dimension. Left([Date-Time], 10) may be easiest, since it works in the calculated dimension with no edits in the script.
You can do it completely in the application (no script work). Start with ID as a dimension and then create another dimension: Date([Date-Time]). Then create an expression: Avg(Value).
EDIT: I ended up using Date([Date-Time]) in the load and then using Date#([Date-Time]) as the calculated dimension. Left([Date-Time], 10) may be easiest, since it works in the calculated dimension with no edits in the script.
It worked. Thanks so much!