topic Re: Above function group by dimension in QlikView

Above function group by dimension

Sun, 19 Jan 2014 17:27:31 GMT

Hi All

I have a requirement of replacing nulls with previous value which I am able to achieve by above function.

Is there any option to limit it only to date dimension and group by on other dimension.

Thanks in Advance

Veena

Re: Above function group by dimension

Sun, 19 Jan 2014 22:07:31 GMT

Veena,

In fact, you need to order your table by Other Dimensions and Date

And also, use the peek() or previous() functions, sth like:

if (NOT isnull(Price), Sales, if(previous(Customer)=Customer AND previous(Product)=product, peek('Price')) ) as 'Price',

[...]
FROM [...]

ORDER BY Customer, Product, Date;

Here, I want to fetch the previous Price only if the Price is null AND if it is the same Customer and the same Client as the previous one.

Fabrice

Re: Above function group by dimension

Mon, 20 Jan 2014 04:12:59 GMT

Hi Fabrice,

I understand that peek function will help in this but it is a static value. We are having a cumulative measure and when displayed over multiple dimension in pivot table results in null values. So we are looking for a dynamic function with which we can achieve the same in expression. Above function is working fine.

[Actual Sale]=IF(ISNULL(MAX(Sales)),Above(([Actual Sale]),1),MAX( Sales))

But I need it group by other dimensions like region. One product sale value should not be carry forward to next region. Is there any way to achieve this.

Thanks in Advance

Veena