topic Re: How to avoid dimension for an expression in QlikView

How to avoid dimension for an expression

kkkumar82 — Fri, 11 Sep 2015 09:33:16 GMT

Hi Qlikers,

I have a straight table with dimension Product and have an expression which calculates lowest Transformation for each product, for that I am using an expression -

Sum({<QuarterYear = {$(vNextQuarter),$(vNextQuarter1),$(vNextQuarter2),$(vNextQuarter3)}>}[Transformation Product Actual]*[Transformation Lowest Cost])

Sum({<QuarterYear = {$(vNextQuarter),$(vNextQuarter1),$(vNextQuarter2),$(vNextQuarter3)}>}[Product Spend])

Now In the next column I want to have for each product the lowest transformation% across all products.

How to change the above expression so that it gives me the lowest for all products.

I am attaching a sample file for understanding

Thanks

Kiran Kumar

Re: How to avoid dimension for an expression

sunny_talwar — Fri, 11 Sep 2015 09:47:55 GMT

May be this:

Min(TOTAL Aggr(Sum({<QuarterYear = {$(vNextQuarter),$(vNextQuarter1),$(vNextQuarter2),$(vNextQuarter3)}>}[Transformation Product Actual]*[Transformation Lowest Cost])

Sum({<QuarterYear = {$(vNextQuarter),$(vNextQuarter1),$(vNextQuarter2),$(vNextQuarter3)}>}[Product Spend]), Product))

Re: How to avoid dimension for an expression

kkkumar82 — Fri, 11 Sep 2015 11:01:12 GMT

Hi Sunny,

In place of Total I have used All , which one you suggest All or Total.

Thanks

Kiran Kumar

Re: How to avoid dimension for an expression

sunny_talwar — Fri, 11 Sep 2015 11:04:34 GMT

To tell you the truth, I have never used ALL, so not entirely sure what it does. But I have heard this in the past that ALL might go away in the future versions of QlikView (I do not have any proof or reliable source which can prove that).

I would suggest trying both the expressions (one with TOTAL, other with ALL) and make some selections to see which one gives you the result you are looking for.

HTH

Best,

Sunny

Re: How to avoid dimension for an expression

kkkumar82 — Fri, 11 Sep 2015 11:15:07 GMT

Thanks I will do it.

Kiran Kumar

Re: How to avoid dimension for an expression

sunny_talwar — Fri, 11 Sep 2015 11:18:34 GMT

Awesome