how to write generalized formula in the expression?

[#1]

Hello Everyone,

I have problem in writing an expression of a graph where i need to do the following:

Dimension: YEAR

Year: includes 2006,2007,2008,2009,2010,2011

I have data of 6 years. Now , I have to use that and write an expression that can calculate the following:

count(if(year='2006' or year='2007, prodID)) / (count(if(year='2006',prodID)) + count(if(year='2007',prodID))/2) --> this expression will calculate value for the year 2006.

I've to write a generalized formula that can automatically calculate for subsequent years 2007,2008,2009,2010 and so on. So, my graph will have the dimension as YEAR and it will calulate the value for different years through a formula which I am gonna write as Calculated expression.

Its something like a incremental variable, because I can't write 6 formulas for each year.

Thanks in advance.

[IAMDV]

Hi,

You can use dollar sign expansion within QV. I mean you will write one generic expression inside the variable and you can pass the values. This works same as UDF. Here is one example of what I am referring to...

SUM({$} $1 ) & ' / ' & 1000000

In the above example $1 can be replaced by any field. Please check this url for one example...

http://qlikviewmaven.blogspot.com/2011/04/variable-that-acts-like-user-defined.html

Cheers - DV

[#3]

Hi,

Thanks for the reply. But I didn't understand how can I use a dollar expression. I've never used it and I am still not sure how can I use this, in the case I mentioned above ??

Thanks again

[#4]

Hi,

Suppose I am gonna use the $ expression. How can I use a if expression? It would be great if you can tell me what values goes into variables and what goes into expression for the formula below.:

count(if(year='2006' or year='2007, prodID)) / (count(if(year='2006',prodID)) + count(if(year='2007',prodID))/2)

Should I go for 3 variables and set there values to each of the 3 counts I am performing. Then writing expression.

Also , I am using "OR" in my 1st count, how to write a variable for that?

Sorry for the wierd question above..but I am trying to figure out something from the example you posted for $ expresion.

Thanks.

[IAMDV]

Hi Mate,

Sorry if I had confused you with my previous post. Please can you post the QV document with your working expression (ofcourse, only sample data). So that I can work something using Dollar Sign Expansion.

Cheers - DV

[Miguel_Angel_Baeyens]

Hi,

Assuming your table is something like

Data:
LOAD Repeat(Chr(64 + Ceil(Rand() * 5)), 3) AS ProdID,
     2000 + Ceil(Rand() * 11) AS Year
AUTOGENERATE 20;

Create a new chart, select type Straight Table, use ProdID as Dimension and set the following as expression:

(Count(ProdID) + Below(Count(ProdID)))
/
(Count(ProdID) + (Below(Count(ProdID)) / 2))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Hi ,

Attached is the file that I am working on right now. I am facing problem in calculating the turnover percentage (not with the products this time, but with the employees). So if you could tell me a way to do that. The count graph is actually not giving me the percentage because in the expression i have used:

count(if(isnull(TermDate)=0,Emplid))/count(Total<DateRange, Sex> Emplid)

you can see the defination for daterange in script. So my actual data will have data recorded for each date like, the there will be a snapshot on each date starting date from finanacial year 09 to end of 09. then starting of 10 and so on,

so..my expression should calculate percentage based on the above formula, but in the formula should do something like this:

(Countall employees whose term date is not null ) /

((count employees based on diff dimension as date range , sex)/total distinct effective date in each financial year))

So after this what we are doing is, calcultaing the total employees on each date for the FY-09 and then just diving it by the total number of distinct effective dates( we cant use 365). I dont know what is the best way to do these type of calculations.

I am sorry if I am not clear with the question. But i have tried many things and not able to do this.

any help would be appreciated. thanks alot.

[#8]

can we setup a variable that can count total no of distinct effective dates ..in any financial year??