topic Re: Problem with Only function in QlikView

Problem with Only function

Anonymous — Mon, 15 Dec 2014 16:09:46 GMT

Hi community,

I have a trouble using only function in a chart expression. My dimension is dimension1, and my expression is like:

=sum({<flag={'1'}, dimension2={'$=(Only(dimension1))'}>} Sales)

dimension1 and dimension2 are same format, result of MonthName function in script.

I tried with ' and " and without them, but still not working.

I'd appreciate any help. Thanks.

Marc.

Re: Problem with Only function

MK_QSL — Mon, 15 Dec 2014 16:12:53 GMT

Try

=sum({<flag={'1'}, dimension2={"=dimension2 = dimension1"}>} Sales)

Re: Problem with Only function

Anonymous — Mon, 15 Dec 2014 16:19:18 GMT

Thanks Manish, but it works only for one unique value selected in dimension1.

Re: Problem with Only function

MK_QSL — Mon, 15 Dec 2014 16:21:00 GMT

Provide few lines of sample data.. may be 5-6 enough

Re: Problem with Only function

Anonymous — Mon, 15 Dec 2014 16:28:56 GMT

Manish, PFA.

I've excluded flag dimension to make it easy.

Marc

Re: Problem with Only function

MK_QSL — Mon, 15 Dec 2014 16:39:06 GMT

Create a Chart

Dimension

Dimension1

Expression

SUM(IF(dimension1 = dimension2,Sales))

Re: Problem with Only function

Anonymous — Mon, 15 Dec 2014 16:58:08 GMT

Yes, it works here but it is not going fine with the other conditions in the set analysis. Tomorrow I will prepare another sample data with more info and fields to analize it deeper.

Thanks a lot, Manish.

Marc.

Re: Problem with Only function

Anonymous — Tue, 16 Dec 2014 09:54:44 GMT

Manish,

PFA.

The white background zone is the expected value (linked to an alternate estate). Total in left table for november 2014 should match the value in the right side.

Thanks.

Re: Problem with Only function

MK_QSL — Tue, 16 Dec 2014 10:00:20 GMT

I am finding difficulties to understand due to field name... Could you please create a sample excel file with expected output?

Re: Problem with Only function

Anonymous — Tue, 16 Dec 2014 10:14:29 GMT

Sorry Manish. I've renamed field. You could see expected output for november 2014 in the table added in the right side.

Thanks.

Re: Problem with Only function

MK_QSL — Tue, 16 Dec 2014 10:34:02 GMT

This should work...

=SUM(IF(Country = CountryField and Flag = 1 and dimension1 = dimension2, Sales))

Re: Problem with Only function

Anonymous — Tue, 16 Dec 2014 10:37:18 GMT

Manish, this expression is exactly what I have...

Re: Problem with Only function

Anonymous — Tue, 16 Dec 2014 12:27:47 GMT

Manish,

If the values in the isolated tables would be Distinct, it works fine. Anyway, I give up. I'll try from another point of view.

Thanks for your time.

Marc.

Re: Problem with Only function

MK_QSL — Tue, 16 Dec 2014 12:29:01 GMT

Busy with my project... so can't spend much time on this... Can try on coming weekend...

Re: Problem with Only function

Colin-Albert — Tue, 16 Dec 2014 18:52:53 GMT

Only() will return a value when the field has just one selected value.

If no values are selected or more than one value is selected then Only() will return Null.

It may be better to use Max() or Maxstring() instead of Only(), as the max expressions will always return a value whether one value is selected, or multiple values are selected, or no values are selected.