Set Analysis Date Filter (Qlik Sense)

[#1]

I need help with this issue I'm having.

First off all lets talk about my data

this are my formats

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';

I declare this variables

SET vAnioActual=YEAR(NOW());

SET vAnioAnt=YEAR(NOW())-1;

SET vMesActual=MONTH(Today());

SET vFechaHoy=Now();

SET vFechaAnioAtras = Date#(Now()-365,'DDMMYYYY');

When I bring my data this are my fields that brings date data

LOAD Documento,

    Fecha,

    Date#(Fecha,'DDMMYYYY') As FechaNum,

    YEAR(Fecha) As Anio,

    MONTH(Fecha) As Mes,

     TotalVta............

Where Fecha is my field containing dates in dd/mm/yyyy format

I've been trying to get info of my sales (TotalSales) From the last 12 months from now, I've been trying using, variables, a constant and formulas, but not working at all.    I Attached my App.   Please help

Thanks in advance

[sunny_talwar]

When I bring my data this are my fields that brings date data

LOAD Documento,

    Fecha,

   Date#(Fecha,'DDMMYYYY') As FechaNum,

    YEAR(Fecha) As Anio,

    MONTH(Fecha) As Mes,

    TotalVta............

Where Fecha is my field containing dates in dd/mm/yyyy format

From your script above, it doesn't seem like Fecha is in dd/mm/yyyy format, it seems to be in ddmmyyyy meanding 31012015 instead of 31/01/2015. Which of the two is right???

QlikView and Qlik Sense should have same Set Analysis syntax I believe (not an expert in Qlik Sense)

Best,

Sunny

[#3]

The field you are refering to is FechaNum

I'm refering to Fecha (located, right before FechaNum)

[sunny_talwar]

I know, but if Fecha is DD/MM/YYYY then FechaNum will not be calculated properly.

You will need this for FechaNum:

Num(Date#(Fecha, 'DD/MM/YYYY')) AS FechaNum

and just so Fecha is read properly, try doing this:

Date(Date#(Fecha, 'DD/MM/YYYY')) as Fecha

See if that helps, if not what is the expression you are trying to use?

[#5]

Ok I tried it, and use this formula for my line graph, but still is showing all month data and all years data, not filterin

This is the formula:

SUM({<Anio=,Mes=,Fecha = {'>=$(=YearStart(Today())) <= $(=Today())'}>} TotalVta)

[sunny_talwar]

Try this:

=SUM({<Anio=, Mes=, Fecha = {"$(='>=' & Date(YearStart(Today())) & '<=' & Date(Today()))"}>} TotalVta)

[#7]

the same result, please see attached image, after writing your formula

[sunny_talwar]

I am guessing we are talking about the bottom chart???

Can you disable all the expression and show a straight table for the only the one expression we are trying to troubleshoot? And if possible I would add Year in the dimension to check if previous years are not flowing in.

[#9]

Fecha has a Null Value

This is the way I upload the Field Fecha

Date(Date#(Fecha, 'DD/MM/YYYY')) as Fecha,

    Num(Date#(Fecha, 'DD/MM/YYYY')) AS FechaNum,

Thanks for your help

[sunny_talwar]

I guess was Fecha is not DD/MM/YYYY then, may be it DDMMYYYY

Can you reload with just Fecha and checking what format it is? (May be attach a screenshot for Fecha

Best,

Sunny