topic Re: Rolling 12 days in QlikView

Rolling 12 days

Anonymous — Wed, 13 Feb 2013 08:03:28 GMT

I need to create formula that counts a measure the last 12 days.

It should allways be tha last 12 days even if one select other periods from timetable.

I tired this one:

COUNT(DISTINCT if([Date record was created.] >= date(num(today()) -12) and TO_LOCATION LIKE 'INN_MONO_0*' and ((not([UOM for FROM_QTY.] like 'RC')) and FROM_LOCATION LIKE '1*'),TO_LIC

,0))

My original formula

COUNT(DISTINCT if(TO_LOCATION LIKE 'INN_MONO_0*' and ((not([UOM for FROM_QTY.] like 'RC')) and FROM_LOCATION LIKE '1*'),TO_LIC

,0))

Please advise.

Thanks for your time.

Dan

Re: Rolling 12 days

alexandros17 — Wed, 13 Feb 2013 08:11:39 GMT

The first formula looks good, if it doesn't work pay attention to date format ('DD/MM/YYYY') for example

Re: Rolling 12 days

Sokkorn — Wed, 13 Feb 2013 10:17:32 GMT

Hi Dan,

Let try this (ignor syntax checker)

Count(
    {$<[Date record was created.] = {">=$(=Date(Today()-12))"},
    [TO_LOCATION] = {'INN_MONO_0*'}>},
    [FROM_LOCATION] = {'1*'}>},
    [UOM for FROM_QTY.] -= {'*RC*'}>}
Distinct [TO_LIC])

Hope it help

Regards,

Sokkorn

Re: Rolling 12 days

SunilChauhan — Wed, 13 Feb 2013 11:07:32 GMT

Try Below code

count({<[Date record was created.] ={">$(=Date(addmonths(max([Date record was created.]),-12))<={$(=Max([Date record was created.])}"},[TO_LOCATION] = {'INN_MONO_0*'}>}, [FROM_LOCATION] = {'1*'}>}, [UOM for FROM_QTY.] -= {'*RC*'}>}>}Distinct [TO_LIC])

hope this helps

Re: Rolling 12 days

Qlik__Vinodh — Wed, 13 Feb 2013 11:11:05 GMT

HI i have an issue friend can u help me yarr................

Re: Rolling 12 days

Qlik__Vinodh — Wed, 13 Feb 2013 11:15:52 GMT

Hi Sokkorn,

i am asking u man .i need quarter and previous year-quarter comparision is required,so pls help me .

Re: Rolling 12 days

Gabriel — Wed, 13 Feb 2013 11:18:04 GMT

Hi,

Check if the attached helps

Re: Rolling 12 days

Anonymous — Wed, 13 Feb 2013 15:02:56 GMT

This one seems to go 12 months back, where I would need only 12 days.

Dan

Re: Rolling 12 days

Anonymous — Wed, 13 Feb 2013 15:03:08 GMT

This one seems to go 12 months back, where I would need only 12 days.

Dan

Re: Rolling 12 days

Anonymous — Wed, 13 Feb 2013 15:03:43 GMT

This one seems to go 12 months back, where I would need only 12 days.

Dan

Re: Rolling 12 days

Sokkorn — Thu, 14 Feb 2013 01:08:27 GMT

Hi Dan,

How about this one

Count(

{$<[Date record was created.] = {">=$(=Date(Today()-12))<=$(=Date(Max([Date record was created.])))"},

[TO_LOCATION] = {'INN_MONO_0*'}>},

[FROM_LOCATION] = {'1*'}>},

[UOM for FROM_QTY.] -= {'*RC*'}>}

Distinct [TO_LIC])

Regards,

Sokkorn

Re: Rolling 12 days

Sokkorn — Thu, 14 Feb 2013 01:14:34 GMT

Hi c vr,

Can you details your requirement? We are not clear what you looking for yet.

BTW, I have some clue below:

1. Sales for the same Month but Previous Year:

Sum(

{$<MonthID = {$(=Max(MonthID) – 12)},

Year = ,

Quarter = ,

Month = >} Sales)

2. Sales for same Quarter of the Previous Year:

Sum(

{$<QuarterID = {$(=Max(QuarterID) – 4)},

Year = ,

Quarter = ,

Month = >} Sales)

Regards,

Sokkorn

Re: Rolling 12 days

Anonymous — Thu, 14 Feb 2013 13:14:58 GMT

Thanks, still some issues, Both this got me a lot closer. Had to change the dateformat.

How do I get it to allow me to look at two from_loactions at the same time

this is the origianl:

=count( {$<DatoKortOmv = {">=$(=Date(Today()-12))<=$(=Date(Max(DatoKortOmv)))"},

[TO_LOCATION] = {'INN_MONO_0*'},

[FROM_LOCATION] = {'2*'},

[UOM for FROM_QTY.] = {"*"} - {'*RC*'} >} distinct

[TO_LIC])

BUT I need something like

=count( {$<DatoKortOmv = {">=$(=Date(Today()-12))<=$(=Date(Max(DatoKortOmv)))"},

[TO_LOCATION] = {'INN_MONO_0*'},

[FROM_LOCATION] = {'2*'} or [FROM_LOCATION] = {'2*'},

[UOM for FROM_QTY.] = {"*"} - {'*RC*'} >} distinct

[TO_LIC])

Gives me nothing if I do it like that.

I could just do formula1 + formula2 and just change the from_location value. But I am sure there is a better way,

Dan

Re: Rolling 12 days

Gysbert_Wassenaar — Thu, 14 Feb 2013 13:20:07 GMT

[FROM_LOCATION] = {'2*'} or [FROM_LOCATION] = {'2*'}

These two conditions are identical so you need only one

But if you want something like [FROM_LOCATION] = {'2*'} or [FROM_LOCATION] = {'6*'} you can use [FROM_LOCATION] = {'2*','6*'}