Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- A function with dollar sign difference to a "funct...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Applicable88

Creator III

2020-06-09
07:12 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

917 Views

1 Solution

Accepted Solutions

tresesco

MVP

2020-06-10
08:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

_ylc

Partner - Creator

2020-06-10
12:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Creator III

2020-06-10
01:30 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

892 Views

tresesco

MVP

2020-06-10
01:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

887 Views

Applicable88

Creator III

2020-06-10
06:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

864 Views

tresesco

MVP

2020-06-10
08:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Creator III

2020-06-10
09:37 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

836 Views

tresesco

MVP

2020-06-10
10:10 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Community Browser