Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.

Issues using variables/functions in set analysis

agigliotti
Honored Contributor II

Issues using variables/functions in set analysis

Hello,

I have the below dimension in pivot table:

Aggr( Count ( DISTINCT {<[Origine DATI] = {I,O,S,F}, [Flag statistiche C21] = {S}, [Flag statistiche C58] = {T, Q, V},

[Data Ordine] = {"$(=Today())"}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),

  [Codice Agente])

It works fine in QlikView but doesn't works in Qlik Sense.

I tried to replace the Today() function with 06-30-2015 and it works fine.

But of course I need to use Today() function.

Can anyone help me ?

Attached you can see the real use case, to achieve the pivot table on the right I used 07-03-2015 in set analysis instead of today() function.

Also take a look at second attachment, it's the error I get using today() function in set analysis.

Many thanks in advance.

Best Regards

Andrea

Comments
MVP
MVP

Try

Aggr( Count ( DISTINCT {<[Origine DATI] = {I,O,S,F}, [Flag statistiche C21] = {S}, [Flag statistiche C58] = {T, Q, V},

[Data Ordine] = {"$(=Date($(=Today()),'MM-DD-YYYY'))"}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),

  [Codice Agente])

agigliotti
Honored Contributor II

No it doens't works, I still get the Error (Unknown error) message.

MVP
MVP

where you use this expression?

May be one more aggregation missing

SUM(Aggr(Count(Distinct {<YourSetExpression>}Something),Dimension))

agigliotti
Honored Contributor II

I'm using it as dimension in pivot table.

The same works as expected in QlikView.

I'm worried about it could be a bug.

Please help me.

Thanks.

ely_malki
Contributor

Hi Andrea,

your issue  is not a bug, but it's only Data format problem.

In your Qlik Sense default "local settings" variable you can easly find this definition (Script BackEnd) :

SET DateFormat='DD/MM/YYYY';

This variable define data format in your Qlik Sense App.

In your expression you try to use a different format DD-MM-YYYY.

Your Expression need to be like this if your field, [Data Ordine], data format is DD-MM-YYYY :

Aggr( Count ( DISTINCT {<[Origine DATI] = {I,O,S,F}, [Flag statistiche C21] = {S}, [Flag statistiche C58] = {T, Q, V},[Data Ordine] = {'$(=Date(Today(),'DD-MM-YYYY'))'}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),[Codice Agente])

Your Expression need to be like this if your field, [Data Ordine], data format is MM-DD-YYYY :

Aggr( Count ( DISTINCT {<[Origine DATI] = {I,O,S,F}, [Flag statistiche C21] = {S}, [Flag statistiche C58] = {T, Q, V},[Data Ordine] = {'$(=Date(Today(),'MM-DD-YYYY'))'}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),[Codice Agente])

Regards,

Ely Malki

MVP & Luminary
MVP & Luminary

You have a number of string values in set analysis that look like they should be encased in single quotes.

I think Manish is on to something with the inclusion of the Date function to make this come back with the correct format date.  You can simplify this a bit though, with:

$(=Date(Today(),'MM-DD-YYYY'))


This may be the difference between it working or not.

Also, the quotes around the date should be single and not double quotes - as it is an exact match on the date, rather than a wildcard match.

The format of the date in Set Analysis is always critical.  When you typed the date manually was it in MM-DD-YYYY?  Whatever format it was in, ensure the parameter of the date function is set the same.

Steve

agigliotti
Honored Contributor II

Just I told before typing manually the date value as 07-06-2015 it works well.

I followed your syntax as below:

Aggr( Count ( DISTINCT {<[Origine DATI] = {I,O,S,F}, [Flag statistiche C21] = {S}, [Flag statistiche C58] = {T, Q, V},

[Data Ordine] = {"$(=Date(Today(),'MM-DD-YYYY'))"}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),

  [Codice Agente])

but it still not working with the same error message.

MVP & Luminary
MVP & Luminary

It is possible that the output of the Today function is different in Qlik Sense to QlikView.  It seems unlikely, but it would be worth testing.

Suggest you add a text area to the Sense sheet, with the expression simply:

Date(Today(),'MM-DD-YYYY'))


See what you get from that.


Cheers,

agigliotti
Honored Contributor II

in the script I have SET DateFormat='DD-MM-YYYY';

and my expression in pivot table is like below:

Aggr( Count ( DISTINCT {<[Origine DATI] = {'I','O','S','F'}, [Flag statistiche C21] = {'S'}, [Flag statistiche C58] = {'T','Q','V'},

[Data Ordine] = {"$(=Date(Today(),'DD-MM-YYYY'))"}, [Tipo provvigioni] = {'+'} >} [Numero Ordine]),

  [Codice Agente])

but it doesn't works "Unknown error" !!!

agigliotti
Honored Contributor II

I get the value 07-06--2015 while using Date(Today(),'DD-MM-YYYY') I get 06-07-2015

Version history
Revision #:
1 of 1
Last update:
‎06-30-2015 11:46 AM
Updated by: