
Re: how to write generalized formula in the expression?
Deepak Vadithala Jun 28, 2011 4:50 AM (in response to yaman1510)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/variablethatactslikeuserdefined.html
Cheers  DV

how to write generalized formula in the expression?
yaman1510 Jun 28, 2011 1:57 PM (in response to Deepak Vadithala)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

how to write generalized formula in the expression?
yaman1510 Jun 28, 2011 2:48 PM (in response to yaman1510)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.

Re: how to write generalized formula in the expression?
Deepak Vadithala Jun 29, 2011 4:39 AM (in response to yaman1510)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

Re: how to write generalized formula in the expression?
Miguel Angel Baeyens de Arce Jun 29, 2011 5:55 AM (in response to yaman1510)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.
BI Consultant

Re: how to write generalized formula in the expression?
yaman1510 Jul 12, 2011 7:52 PM (in response to Miguel Angel Baeyens de Arce )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 FY09 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.

test1.xlsx 9.7 K

test1.qvw 158.2 K

Re: how to write generalized formula in the expression?
yaman1510 Jul 13, 2011 1:57 PM (in response to yaman1510)can we setup a variable that can count total no of distinct effective dates ..in any financial year??




