Solved: A function with dollar sign difference to a "funct... - Qlik Community

Applicable88 · ‎2020-06-09

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.

tresesco · ‎2020-06-10

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.

View solution in original post

_ylc · ‎2020-06-10

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?

Applicable88 · ‎2020-06-10

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?

tresesco · ‎2020-06-10

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.

Applicable88 · ‎2020-06-10

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?

tresesco · ‎2020-06-10

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.

Applicable88 · ‎2020-06-10

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

tresesco · ‎2020-06-10

{<"$(=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.

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