topic Re: Set Analysis - Function Inside Function in QlikView

Set Analysis - Function Inside Function

Thu, 01 Sep 2011 11:15:10 GMT

Hi All,

I have the following set analysis function

count({<PayRate={"$(=min(Total<ClientName,TempName,CategoryDesc,Invoice>PayRate))"}>}TOTAL<ClientName,TempName,CategoryDesc,Invoice>ChargeRate)

The function in the "" works perfectly when done on its own. As an example it gives me a value of 27 on the first row. I want to use this "dynamic" function in a set analysis expression.

I want my expression to count the number of times there is a charge rate for the min value over the following aggregation. I have used the TOTAL function as I need this to display on all rows.

What am I doing wrong folks

Thanks,
Byron

Re: Set Analysis - Function Inside Function

Miguel_Angel_Baeyens — Thu, 01 Sep 2011 11:20:48 GMT

Hi Byron,

Try instead

Count({< PayRate = {"=Min(TOTAL <ClientName, TempName, CategoryDesc, Invoice> PayRate)"} >} 
     TOTAL <ClientName, TempName, CategoryDesc, Invoice> ChargeRate)

Removing the "$(=)" characters.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis - Function Inside Function

Thu, 01 Sep 2011 11:27:25 GMT

Hey Miguel,

I get the same value back, 0. I know based on the line that I'm looking at that I should see a value 3 appear. There are 3 counts of the min value 27. Whats funny is that if I replace the set analysis with {'27'} i get 3, so I know thats the answer I'm looking for.

Any ideas????

PS: How do you insert that expression box. I see you use these for a lot of your replies?

Re: Set Analysis - Function Inside Function

Miguel_Angel_Baeyens — Thu, 01 Sep 2011 11:33:40 GMT

Bryan,

Note that set analysis is evaluated once and for the whole chart, so

{"=Min(TOTAL <ClientName, TempName, CategoryDesc, Invoice> PayRate)"}

Will return the Min value for the whole chart, not for the value in your dimension.

So you will likely need to use an If() instead. Anyway, post some sample data and the values you expect so we can check further.

In regards to the box, go to the upper right corner of the editor, click on the "Use advanced editor" and use the ">>" sign, "Syntax Highlighting", "Plain".

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis - Function Inside Function

Thu, 01 Sep 2011 11:55:23 GMT

Once you said it, it clicked. Thats exactly why this is not working! Is there anyway of taking an expression like the min and using it inside of another expression. I tried calling the expression name but that didnt work either. If a test qvd would help you, I can put one together.

Thanks,

Byron

Re: Set Analysis - Function Inside Function

Miguel_Angel_Baeyens — Thu, 01 Sep 2011 12:01:30 GMT

To get an idea of what does it mean, I recommend you to check this thread that discusses the subject and take a look at the attached application, just in case.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis - Function Inside Function

Thu, 01 Sep 2011 13:15:20 GMT

Thanks Miguel, you are a legend sir I stepped away from the aggr function since it was only populating for 1 line. however, your attachments were clever about how you went about using it. Thanks again man, worked like a charm. Below is the code

count(TOTAL<ClientName,TempName,CategoryDesc,Invoice>if(aggr(min(PayRate),ClientName,TempName,WkEndDate,CategoryDesc,Invoice)=PayRate,PayRate))

Re: Set Analysis - Function Inside Function

Thu, 13 Jun 2013 12:26:57 GMT

hi,

i am trying to use the below function in Qlik View and get an error

sum({$<yearmonthvalue

>={201201}>} amount*(1+taxrate))

i need the year moth value where all the data can be pulled greater than and equal to 2012

>=201201

can anyone please help me what is wrong in the expression

Re: Set Analysis - Function Inside Function

Thu, 13 Jun 2013 12:27:04 GMT

hi,

i am trying to use the below function in Qlik View and get an error

sum({$<yearmonthvalue

>={201201}>} amount*(1+taxrate))

i need the year moth value where all the data can be pulled greater than and equal to 2012

>=201201

can anyone please help me what is wrong in the expression