topic Re: Expression in QlikView

Expression

deepakqlikview_123 — Mon, 24 Mar 2014 05:56:58 GMT

Hi All,

Can you please tell me how below expression will work.

dual(count(distinct EnquiryID)/1000 & ' (' & num((count(distinct EnquiryID)/count(distinct total EnquiryID)), '0.00%') & ')', count(distinct EnquiryID)

Thanks,

Re: Expression

Mon, 24 Mar 2014 06:03:35 GMT

it displays the average count of Enquiry id as per the dimention versus the total count of enquiryID ignoring all the dimensions and the result is concatenated into a text enclosed within brackets, and you have the numerical count as well as part of the dual.

Re: Expression

deepakqlikview_123 — Mon, 24 Mar 2014 06:17:43 GMT

I have ques as per below,

How & ' (' & will work

What is use of Dual in above expression

Thanks

Re: Expression

Mon, 24 Mar 2014 06:32:04 GMT

& is for concatenation.

Dual is typically for dual purpose. You have the text value as well as the numeral. If you want to use the expression in a list box or a dimension of a chart for example, it will use the text value. If you use it in an expression it will use the numeral value that is the second argument.

A typical example will be a month, you can have a text value of 'Jan' and also a numeral of 1.

Thanks,

Ram

Re: Expression

MK_QSL — Mon, 24 Mar 2014 07:16:58 GMT

Dear Deepak,

Let me explain you in brief...

Consider the enclosed file showing dummy sales of few customers.

Table 1 : showing customer vs Sales... You can sort the data by clicking on Sales Column

Table 2 : showing customer vs %SalestoTotal. Here also you can sort the data.

Table 3: Now consider that you want to show Sales & % sales together in an expression...

Sales = SUM(Sales)
% Sales = SUM(Sales)/SUM(Total Sales)

As you can see that 1st is having Number and 2nd is having % format, you can't use the number tab.

This can be achieved using NUM(YourExpression, '#,##0.00') and NUM(YourExpression, '#,##0.00%')

to separate these two expression we can use

1st Expression &' (' & 2nd Expression &')'

But now question is how to sort by SUM(Sales) by simply clicking on the Column.

Table 4: This can be achieved using Dual Funciton.

Dual function is Creating a forced association between a string (1st Expression) and a number (2nd Expression)

Hope this helps...

Re: Expression

deepakqlikview_123 — Mon, 24 Mar 2014 08:20:00 GMT

Thanks manish for giving me explanation

Re: Expression

Anonymous — Sat, 26 Apr 2014 23:06:14 GMT

Hello Manish:

I am trying to use your suggestion for formatting of a number and a percentage in a dual expression. The expression is as follows:

=dual( Sum([OMAFee]) & ' (' & num((Sum([OMAFee]))/Sum(Total [OMAFee])), '##.0#') & ')',

Sum([OMAFee]) + (sum[OMAFee))/(Sum(Total [OMAFee]))/100 )

The preceding gives me Bar Chart values that look like: Anaesthesia 40426.31 (2.1%)

I am using QV Personal Edition and cannot read foreign .qvw files. Can you give me the expression for changing the preceding output so that I get the $ value edited correctly as: $40,426.31

Thanks very much

David

Re: Expression

MK_QSL — Sun, 27 Apr 2014 06:54:39 GMT

Try

=dual( Money(Sum([OMAFee]),'$#,##0.00') & ' (' & num((Sum([OMAFee]))/Sum(Total [OMAFee])), '##.0#') & ')',

Sum([OMAFee]) + (sum[OMAFee))/(Sum(Total [OMAFee]))/100 )

For the question, asked in this thread, we can do like below...

DUAL(MONEY(SUM(Sales),'$#,##0.00') & ' ('&NUM(SUM(Sales)/SUM(TOTAL Sales),'#,##0.00%')&')',NUM(SUM(Sales)))

Re: Expression

Anonymous — Sun, 27 Apr 2014 13:57:56 GMT

Hi Manish:

Thanks for your suggestion. It works just fine and helps me to learn something good every day!!

I am much obliged

David

Re: Expression

MK_QSL — Sun, 27 Apr 2014 15:05:36 GMT

My pleasure...

Happy Qliking!!!