Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have tables A,B and C in script.
A table have columns
[NO],
[ENTER],
[TO],
[DATE],
[CONSULTANTBY],
[COUNTRY_ID]
B Have Columns
[NO],
[OVERDATE],
[START],
[END],
[LOCATION],
[LABEL]
C have Columns
[ADDNO],
[TO_I],
[WORKER]
there is join between three tables. I need to create a straight / pivot table with below:
Dimension1 : [TO] where TO='Available' and [LABEL]= 'All files completed'
Dimension2 : [WORKER]
YEARMONTH : Year and Month this should be from START and END
Expression : Avg([DATE] - [OVERDATE]) expression should show average days of DATE minus OVERDATE
can any one help me with dimension and expression please
Thanks.
Try this may be:
=Avg(Aggr(DATE - OVERDATE, TO, WORKER, YEARMONTH)) //Assuming that YEARMONTH is also a dimension.
HTH
Best,
Sunny
Hi,
Use Avg(Interval(Date-OverDate,'D'))
regards
Or you can try
Avg(Num(Date-OverDate))
Regards
But wouldn't the DATE - OVERDATE will output a number?
I am not trying to argue that your answer is incorrect, but trying to make sense of the requirement. I don't even know if the solution I proposed will work or not
Hi,
If DATE and OVERDATE is proper Date in QlikView then whenever it load in QlikView it Convert it into Number therfore i suggest to use num()
even she can try
Avg(Num(Date)-Num(OverDate))
regards
Thanks. with out using dimension how to change the expression?
Did you try one of the solutions proposed by max dreamer??? If those did not work, would you be able to share a sample??? Cause I feel you don't need to use aggregate function. The solution should work unless you are using a pivot table and you are looking to get Total row fixed.
Best,
Sunny
Thanks. I have used this. for overall average it is showing figure ok. but when I select individual NO the average is showing more. do I need to change anything here? please let me know.
Num(Date((Date),'DD/MM/YYYY')-Date(OverDate,'DD/MM/YYYY'))