topic Re: time range for avg date in QlikView

time range for avg date

Thu, 18 Oct 2012 09:41:05 GMT

Hi everyone,

i got stuck with a huge problem. ok let me explain.

I have a lot of products and every product has a PoductID and every ProductID can have a lot of ShippingDates.

Product ID ShippingDate

123 20.03.2012

123 25.04.2012

123 10.10.2012

345 02.01.2012

345 09.08.2012

What I want to do now is create an pivot table with the dimensions StoreName and ProductID and the expression Profit. ok that actually sounds easy but now there is my actual problem. I only want to show products their average date was in a specific time range. and I dont know how to achive how to set the timerange for the average shipping date. so lets say my timerange is 01.03.2012 - 25.05.2012 so in my chart only the ProductID 123 should be shown cause the avg date is 22.04.2012 (this number isnt right just made up to make my problem clear). I just don't know how to build the chart right because in set analysis i cant use avg(shippingdate) = {...}

so does anyone know how i could do this?

regards,

Re: time range for avg date

Anonymous — Thu, 18 Oct 2012 09:46:07 GMT

Hm,

Since you have several line items for each order (since there are multiple shipping dates), do you have a table above that in the hiearchy with each individual order represented as one row of data?

In that case I would probably suggest calculating the "average date" for each order in the higher level table and then just using this value for the chart.

Re: time range for avg date

Thu, 18 Oct 2012 09:51:43 GMT

I don't have a tabe above this one in the hierachy! the problem bothers me now for a couple of days and I don't have any idea left how to solve it i tried aready a couple things I used a if in the expression saying if (shipping date > Date1 and shippingdate > date2, sum(profit)) but that only works if there is no storeID so i really don't know how to continue and this is just one example out of many what i have tried.

regards,

Re: time range for avg date

Thu, 18 Oct 2012 10:29:16 GMT

please can anyone help me it is very important

regards,