Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Arnaud35
Contributor III
Contributor III

Set analysis with yearend

Hello,

I have a problem with the formula below :

I wanted to filter the results on the year only

=sum(if([Opportunite.Statut_Commercial]='En cours',[Opportunite.Date_de_Commande]<yearend((31/12/2022)),[Opportunite.Montant_offre_finale]))

 

Many thanks for your help

10 Replies
anat
Master
Master

=sum({<[Opportunite.Statut_Commercial]={'En cours'},[Opportunite.Date_de_Commande]={"<=$(=date(yearend(31/12/2022)))"}>},[Opportunite.Montant_offre_finale])

Arnaud35
Contributor III
Contributor III
Author

Hello,

Thnak you for your proposal.

Unfortunately, i have a mistake as shown below on the screeshot

 

Arnaud35_0-1645009146440.png

 

bensca04
Contributor III
Contributor III

One "=" is missing.

{"<=$(=date(yearend(31/12/2022)))"}

Arnaud35
Contributor III
Contributor III
Author

Arnaud35_0-1645018056176.png

 

A bracket is still missing. I don't understand

 

bensca04
Contributor III
Contributor III

Remove the comma behind the set analysis

Arnaud35
Contributor III
Contributor III
Author

Thank you for your correction.

 

I don't understand because, the date calculated is 1899 instead of 2022 as i wrote

Arnaud35_0-1645019666665.png

 

 

bensca04
Contributor III
Contributor III

The date must be between apostrophs ('31/12/2022') and the date format have to be the same as specified in the SET DateFormat statement at the top of your load script.

Arnaud35
Contributor III
Contributor III
Author

I have no result even if the formula seems to be correct :

Arnaud35_0-1645517393160.png

 

Arnaud35_1-1645517426473.png

 

Arnaud35
Contributor III
Contributor III
Author

I modified the formula as below :

=sum({<[Opportunite.Date_de_Commande]=yearend({"=$(=Date(Today(), 'DD/MM/YYYY'))"}),[Opportunite.Statut_Commercial]={"En Cours"}>}[Opportunite.Montant_offre_finale])

I have an error :

Arnaud35_0-1645635910903.png