Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Creator III

## A function with dollar sign difference to a "function" in quotes

Hello,

I asked this before, but I still don't get it fully, the difference when a function is put after a Dollar Sign Expansion or just in quotes as a so called "search string"

I have a sample table here.

 Customer ID Date Department PrimeAmount 1 01.01.2020 AA 1000 2 01.01.2020 AA 2000 3 01.01.2020 BB 2000 4 02.02.2020 BB 3000 5 02.02.2020 CCC 3000 6 02.02.2020 CCC 5000 7 03.01.2020 DD 5000 8 03.01.2020 EE 2000 9 03.01.2020 FF 1000 10 02.01.2020 GG 1000

I loaded into Qliksense Desktop and played with it and creating another row like:

sum({<Date={"\$(=count(distinct Date))"}>} PrimeAmount)

vs.

sum({<Date={"=count(distinct Date)"}>} PrimeAmount)

I get zero or the same as when I just did sum(PrimeAmount). I know its different, but none of my examples worked.

Can someone make a expression structure with these  4 columns and show me the difference in return of a function in \$expansion and one just in quotes?

Thanks in advance.

Best.

1 Solution

Accepted Solutions
MVP

Let me try to explain. Say one set analysis is :

Sum({<Customer={"=Count(TransactionID)>10"}>}Sales)

This means, it would sum sales values for those customers that have more than 10 transactions. That means, you can think of a table chart here with Customer as dimension and Count(TransactionID) as expression. Now say, that table would be like :

 Customer Count(TransactionID) Sum(Sales) Peter 9 100 Jack 12 200 Rita 15 300 Meg 2 400

So as per  the table the condition ( Count(TransactionID)>10) for Customer satisfies for only Jack(count-12) and Rita (count-15). Hence the final summation of sales would be for these two customers resulting 200+300=500.

@Applicable88 wrote:

..

`{"=Count(DISTINCT Date) > 1"}>}`

is a search string. How can the result of that equals to a order id?

And yes you are right,...result of that expression can't be equal to an order id. But, that is not how set expression works when you use an expression as search string beginning with '='. In fact, then set analysis looks for a TRUE/FALSE condition against that specific dimension being compared with. I.e. it's no more a literal match then.

Hope this helps.

7 Replies
Partner - Creator

1st of all, the result of the set analysis is in number.

Your formula looks like this:

sum({<Date={"4"}>} PrimeAmount)

and thus the result is 0 since there are no Dates with the value of 4. What do you wish to filter in the date here?

Creator III
Author

As an example maybe like this one:

Sum Primeamount of all distinct departments with more than 1. So ....count AA, BB, CCC , but dont count the other one since they only exist one Dataset.

Still my overall question is the difference of \$ vs. only in quotes.

A function can be as a dollar expansion or just in quotes, (as a search string) I just want to know the difference.

Someone respond to me with this referring to another problem:

```Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} Aggr(
Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} DISTINCT Value)
, OrderID))```

And I asked  why {"=Count(DISTINCT Date) > 1"} doesn't need a dollar sign expansion?

Can someone explain in plain english?

MVP

I guessed that I tried to explain this in another post of yours : https://community.qlik.com/t5/New-to-Qlik-Sense/Strange-Expansion-Results-With-double-compare-to-one....

The answer to your question here is:

`` when your set analysis search string begins with an '=', the qlik engine works in the line of advanced search   for the dimension. ``

Just concentrate on advanced search link provide in that post. If you need more clarification, please let know.

Creator III
Author

Thank you @tresesco ,

I wouldn't ask if I would have fully understood it back then.

Maybe I ask it simply:

If today is 10/06=2020

what difference would I get here--->

sum({<Date={="Today()"}>} PrimeAmount) vs.  sum({<Date={\$(="Today()")}>} PrimeAmount)

Or maybe you can explain it to me with this one:

I asked this before, but I still don't get it fully, the difference when a function is put after a Dollar Sign Expansion or just in quotes as a so called "search string"

I have a sample table here.

 Customer ID Date Department PrimeAmount 1 01.01.2020 AA 1000 2 01.01.2020 AA 2000 3 01.01.2020 BB 2000 4 02.02.2020 BB 3000 5 02.02.2020 CCC 3000 6 02.02.2020 CCC 5000 7 03.01.2020 DD 5000 8 03.01.2020 EE 2000 9 03.01.2020 FF 1000 10 02.01.2020 GG 1000

I wanted to know this:

I want to sum the Value of all Order ID only once. Since many other Order ID can also have the same Value, sum(distinct Value) cannot be used.

Second question: In my example every order with the same ORDER ID  also has same date.  Now I want to sum the value of only those with different Dates one time, and another one which only sum these with same dates, in case the table is full of different dates mixed.

```um(Aggr(
Sum(DISTINCT Value)
, OrderID))```

For second question, you can try this

```Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} Aggr(
Sum({<OrderID = {"=Count(DISTINCT Date) > 1"}>} DISTINCT Value)
, OrderID))```

If

`{"=Count(DISTINCT Date) > 1"}>}`

is a search string. How can the result of that equals to a order id?

MVP

Let me try to explain. Say one set analysis is :

Sum({<Customer={"=Count(TransactionID)>10"}>}Sales)

This means, it would sum sales values for those customers that have more than 10 transactions. That means, you can think of a table chart here with Customer as dimension and Count(TransactionID) as expression. Now say, that table would be like :

 Customer Count(TransactionID) Sum(Sales) Peter 9 100 Jack 12 200 Rita 15 300 Meg 2 400

So as per  the table the condition ( Count(TransactionID)>10) for Customer satisfies for only Jack(count-12) and Rita (count-15). Hence the final summation of sales would be for these two customers resulting 200+300=500.

@Applicable88 wrote:

..

`{"=Count(DISTINCT Date) > 1"}>}`

is a search string. How can the result of that equals to a order id?

And yes you are right,...result of that expression can't be equal to an order id. But, that is not how set expression works when you use an expression as search string beginning with '='. In fact, then set analysis looks for a TRUE/FALSE condition against that specific dimension being compared with. I.e. it's no more a literal match then.

Hope this helps.

Creator III
Author

Hello @tresesco ,

thank you very much for your effort. In fact now, I understand what someone told me, when he said that '='  should be more viewed as "with" than "equal" when as a string.

Would you also get an result with  {<"\$(=Count(TransactionID)">10)>} ?

MVP

{<"\$(=Count(TransactionID)">10)>} - would perhaps throw an syntax error. If you meant,

{<"\$(=Count(TransactionID)>10)">} - this wouldn't make much sense, because you are asking to use literal match with true/false or 0/non-zero against  customer values.