topic Re: set expression is not working in QlikView

set expression is not working

Sun, 03 Jul 2016 18:23:16 GMT

I want to calculate the "Population" of a "Country" at "minimum Year".

PFA - screenshot suggest the min year is 1960. So, I want to calculate sum of population at 1960 of a country. please note that country is selected from a list box.

I am using this expression and it is not working:

=sum({<YearPopulation={"=(min(num#(YearPopulation)))"}>}Population)

But when I put =(min(num#(YearPopulation))) in a text box it returns 1960. I am not sure what I am doing wrong in Set Analysis expression. Can you please help?

Re: set expression is not working

Frank_Hartmann — Sun, 03 Jul 2016 18:32:13 GMT

can you share a sample and what is the expected output?

Re: set expression is not working

maxgro — Sun, 03 Jul 2016 18:35:24 GMT

try

sum({$ <YearPopulation={$(=min(YearPopulation))}>} Population)

Re: set expression is not working

swuehl — Sun, 03 Jul 2016 18:36:35 GMT

Try

=sum({<YearPopulation={'$(=(min(num#(YearPopulation))))'}>}Population)

i.e. a dollar sign expansion to evaluate the min() function.

It would also be good to transform the YearPopulation field from text to number in the script.

Re: set expression is not working

trdandamudi — Sun, 03 Jul 2016 18:36:43 GMT

Give a try on the below expression:

=sum({<YearPopulation={"$(=min(num#(YearPopulation)))"}>}Population)

Re: set expression is not working

sunny_talwar — Mon, 04 Jul 2016 01:43:27 GMT

It seems that your YearPopulation is read as text and not Year field. I would suggest converting it to Year in the script like this:

LOAD Year(Date#(YearPopulation, 'YYYY')) as YearPopulation

and then you can try this:

=Sum({<YearPopulation={"$(=Min(YearPopulation))"}>} Population)

In case making modification in the script isn't possible, you can try MinString which will find the Min of Text String:

=Sum({<YearPopulation={"$(=MinString(YearPopulation))"}>} Population)

Re: set expression is not working

Mon, 04 Jul 2016 07:00:02 GMT

Thank you so much!