Date filter doens't work in a set analysis

[hboulnois]

Hello all

Here is my problem :

I use Qlik Sense Desktop. In a KPI, I have two statements :

one for the title :

=Date(MonthEnd(AddMonths(Max(Date_F),-1)),'DD MMMM YYYY')

one for the datas :

=sum( {$< [Date_F] = {"$(=Date(MonthEnd(AddMonths(Max(Date_F),-1)),'DD/MMMM/YYYY'))"},[Client externe]={"-1"} >} (NbVMPwrOff+NbVMPwrOn))

The first statement works great.

The second doen't work (result =0), but I know that it should give something else.

Is there a syntax problem ?

Thanks a lot for any help.

Regards,

Hervé

[sunny_talwar]

There are couple of things to check here

1) Is your Date_F field understood as date by Qlik Sense or not... look here for help on this topic

Why don’t my dates work?

Get the Dates Right

2) What is your Date format from the script? Is it DD MMMM YYYY or something else and you are just displaying in a filter or other object like this.... Set analysis needs the same syntax as you have in the script... For this, you can get some guidance here

Dates in Set Analysis

[hboulnois]

Thank you very much Sunny, this was very helpfull. Owing to these post, I found the issue.Data source is an access file database. The Date_F field has date type and format (dd/mm/yyyy), as you can see in here :

The date stored is this field is the last day of the month. When I want to obtain the previous date (from previous month), I must not use this :

Date(AddMonths(Max(Date_F),-1),'DD MMMM YYYY')

but this :

Date(MonthEnd(Max(Date_F),-1),'DD MMMM YYYY')

However, I think I have also a syntax issue, because this statement works :

=sum(

{$    <

    [Date_F] = {">=$(=Date(MonthEnd(Max([Date_F]), -1), 'DD/MM/YYYY')) <=$(=Date(MonthEnd(Max([Date_F]), -1),'DD/MM/YYYY'))"},

    [Client externe]={"-1"}

    >

}

(NbVMPwrOff+NbVMPwrOn))

And this statement doen't work :

=sum(

{$    <

    [Date_F] = {"$(=Date(MonthEnd(Max(Date_F),-1),'DD/MMMM/YYYY'))"},

    [Client externe]={"-1"}

    >

}

(NbVMPwrOff+NbVMPwrOn))

[sunny_talwar · Accepted solution]

Because the second expression have incorrect format

Have you tried this?

=sum(

{$    <

    [Date_F] = {"$(=Date(MonthEnd(Max(Date_F),-1),'DD/MM/YYYY'))"},

    [Client externe]={"-1"}

    >

}

(NbVMPwrOff+NbVMPwrOn))

[hboulnois]

Thanks a lot !

It works !