topic Re: Set analysis with date limitation in QlikView

Set analysis with date limitation

Fri, 06 Jun 2014 09:02:56 GMT

Hi,

I had a working set analysis expression that limited the date from the start of the fiscal year until today:

sum({$<Abschlusstermin.P= {">=$(=date('$(vFYStart)'),'YYYYMM')<=$(=date('$(vToday)'),'YYYYMM')"}>} [TCV Reportet (Produkt).P])

After a while and several changes to the scripts and the front end, I figured out that the limitation is not working anymore, so it shows all TCV values, not only from FY start to today.

Not sure if this is related to this change: the format for 'Abschlusstermin.P' in the load from was 'YYYYMM', now it's 'MM/YYYY'. I changed the expression to be

sum({$<Abschlusstermin.P= {">=$(=date('$(vFYStart)'),'MM/YYYY')<=$(=date('$(vToday)'),'MM/YYYY')"}>} [TCV Reportet (Produkt).P])

, but this won't work. Any ideas what is going wrong?

Thanks.

Re: Set analysis with date limitation

CELAMBARASAN — Fri, 06 Jun 2014 09:11:02 GMT

Hi,

In Script how you created this format 'MM/YYYY' using date() or combination of Month() and Year()?

Or go and check in the Table viewer, hover the field Abschlusstermin.P and check the tags associated to it whether its $numeric?

Re: Set analysis with date limitation

Fri, 06 Jun 2014 09:14:40 GMT

In the script, i convert from old to new format:

date(date#(SF_TARGET.Monat,'YYYYMM'),'MM/YYYY') as Abschlusstermin.P

The table viewer states Key, Numeric, Integer, Timestamp, Date,

Re: Set analysis with date limitation

CELAMBARASAN — Fri, 06 Jun 2014 09:24:48 GMT

Try this

sum({$<Abschlusstermin.P= {">=$(=date(vFYStart,'MM/YYYY')<=$(=date(vToday, 'MM/YYYY')"}>} [TCV Reportet (Produkt).P])

it will work if vFYStart is created with proper date

Re: Set analysis with date limitation

Fri, 06 Jun 2014 09:28:23 GMT

It is not working, even if I replace both variables with '01.04.2014' and '05.06.2014'.

Re: Set analysis with date limitation

CELAMBARASAN — Fri, 06 Jun 2014 09:42:39 GMT

sum({$<Abschlusstermin.P= {">='$(=date(vFYStart,'MM/YYYY')'<='$(=date(vToday, 'MM/YYYY')'"}>} [TCV Reportet (Produkt).P])

Upload your sample app to look in to this further

Re: Re: Set analysis with date limitation

Fri, 06 Jun 2014 11:09:16 GMT

Ok, I tried to create a simple one. Hope it helps.

Re: Set analysis with date limitation

tresB — Fri, 06 Jun 2014 11:19:36 GMT

=sum({$<Abschlusstermin= {">=$(=date(vFYStart,'MM/YYYY'))<$(=date(vToday, 'MM/YYYY'))"}>} TCV)

Re: Set analysis with date limitation

Fri, 06 Jun 2014 11:46:15 GMT

This is really close to what I'm looking for, thanks a lot.

I also have target values to calculate the same way, problem is for the year-to-date I would need to add the current month, currently only the last two months are calculated.

And for the expression above with the TCV, I also need to calculate to the current day.

How can I do this?

Thanks again.

Re: Re: Set analysis with date limitation

tresB — Fri, 06 Jun 2014 11:50:10 GMT

Just an additional equal sign '=' would be required, like:

=sum({$<Abschlusstermin= {">=$(=date(vFYStart,'MM/YYYY'))<=$(=date(vToday, 'MM/YYYY'))"}>} TCV)

Re: Set analysis with date limitation

Fri, 06 Jun 2014 11:58:58 GMT

This is working fine for the target values, what about the TCV stuff above? It seems that if I add the '=' sign, it calculates for the whole June, but I only need everything until vToday ('06.06.2014').

Re: Set analysis with date limitation

CELAMBARASAN — Fri, 06 Jun 2014 16:22:26 GMT

Then it needs date field, not possible with Year month field. Here you can't decide the data that falls up to today.

May be the below will help you

YTQ, QTD, MTD and WTD

Re: Set analysis with date limitation

Fri, 06 Jun 2014 16:42:37 GMT

You need a date field, not just MMYYYY, to accomplish what you want.

Re: Set analysis with date limitation

Tue, 10 Jun 2014 05:20:56 GMT

I have a date field, too, called SERVICE_DATE.

Re: Set analysis with date limitation

CELAMBARASAN — Tue, 10 Jun 2014 05:24:34 GMT

Then try this

sum({$<Abschlusstermin.P=, SERVICE_DATE= {">=$(=date('$(vFYStart)'),'DD/MM/YYYY')<=$(=date('$(vToday)'),'DD/MM/YYYY')"}>} [TCV Reportet (Produkt).P])

Where 'DD/MM/YYYY' this format should be same as the SERVICE_DATE field date format.