Set Analysis - Function Inside Function

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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?

[Miguel_Angel_Baeyens]

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

[#5]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#7]

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))

[#8]

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

[#9]

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