topic Re: Help with Set Analysis filtering dates in QlikView

Help with Set Analysis filtering dates

Tue, 16 Jun 2015 13:55:46 GMT

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,

TotalSales............

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. Please help

Thanks in advance

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 13:59:41 GMT

try this way,

SUM({<DateDimensionName = {'>=$(=YearStart(Today())) <= $(=Today())'}>} MeasureName)

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 14:08:31 GMT

I change the formula you gave me to this

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

This is the result

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 14:11:32 GMT

is't possible to share ur app?

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 14:17:41 GMT

Shure

Dropbox - Drocaras.qvf

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 15:11:05 GMT

Could you get the App?

Re: Help with Set Analysis filtering dates

jagan — Tue, 16 Jun 2015 15:13:05 GMT

HI,

Try like this

SUM({<Anio=,Mes=, FechaNum=, Fecha = {'>=$(=MonthStart(Today(), -11)) <= $(=Today())'}>} TotalSales)

Hope this helps you.

Regards,

Jagan.

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 15:14:30 GMT

u mean ur asking to share app for the same from my end?

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 15:52:53 GMT

Thanks for the help, but this was the result

This is a link for my app

Dropbox - Drocaras.qvf

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 15:53:42 GMT

I mean if you could download my App from the link I publish

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 15:59:37 GMT

i'm unable to download ur file.

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 16:14:38 GMT

How can I sent you the App?

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 16:17:12 GMT

clik on reply--from top right side --clik on Use advanced editor--from right side bottom --clik on attach--browser ur .qvw file and save

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 16:21:38 GMT

Here it is, thanks for the help

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 16:39:53 GMT

ur file extension is .qvf. so i'm unable to open..can u share .qvw file...pls?

are u tried jagan mohan solution?

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 16:48:06 GMT

Yeap, didn't work neither

I have Qlik Sense I don't know how to transform it as .qvw sorry

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 16:51:10 GMT

ohh..is't,sorry the solution we given hopes work on qlikview..might be little bit different in qliksense.

u can post this query on qliksnes,by select tag as qliksense instead of qlikview,right now this thread on qlikview group.

Re: Help with Set Analysis filtering dates

Tue, 16 Jun 2015 16:55:59 GMT

sorry my bad, I'm new on this, thank you very mucho for your help

Re: Help with Set Analysis filtering dates

buzzy996 — Tue, 16 Jun 2015 16:59:52 GMT

ur wc ,hope u will get answer from qliksense group.

Re: Help with Set Analysis filtering dates

santhoo_san — Tue, 16 Jun 2015 18:31:14 GMT

Hi Try this

SUM( {<Fecha = {">=$(=Min({<Anio={$(=max(Anio))}>} Fecha))"}>} TotalVta)