topic Re: Set analysis before date + other conditions in QlikView

Set analysis before date + other conditions

Tue, 18 Aug 2015 14:13:15 GMT

The formula below is driving me insane

=sum({$<

inv_YearValue={$(=year(today(1))-1)}

, ProductGroupName = {"Widgets"}

, inv_DateValue={'<=$("=Date(addyears(today(),-1))")'}

>}QTYINVOICED*ITEMQTY)

Basically need it to pull data for the previous year, but only until the same day last year as today.

The third condition (inv_DateValue={'<=$("=Date(addyears(today(),-1))")'}) is not having an affect on the data at all. Pulls through same results regardless.

Please help!

Re: Set analysis before date + other conditions

jonathandienst — Tue, 18 Aug 2015 14:15:37 GMT

I think that should be :

inv_DateValue={"<=$(=Date(addyears(today(),-1)))"}

Re: Set analysis before date + other conditions

jonathandienst — Tue, 18 Aug 2015 14:16:40 GMT

Although AddYears returns a Date format, so it could also be

inv_DateValue={"<=$(=Addyears(today(),-1))"}

Re: Set analysis before date + other conditions

sasiparupudi1 — Tue, 18 Aug 2015 14:23:41 GMT

=sum({$<

inv_YearValue={$(=year(today())-1)}

, ProductGroupName = {'Widgets'}

, inv_DateValue={"<=$(=Date(addyears(today(),-1)))"}

>}QTYINVOICED*ITEMQTY)

Re: Set analysis before date + other conditions

maxgro — Tue, 18 Aug 2015 14:26:45 GMT

=sum(

ProductGroupName = {"Widgets"},

inv_DateValue={">=$(=Date(YearStart(AddYears(Today(1),-1))))<=$(=Date(AddYears(Today(1),-1)))"}

>} QTYINVOICED*ITEMQTY)

Re: Set analysis before date + other conditions

Tue, 18 Aug 2015 14:26:59 GMT

Neither are working unfortunately. I have isolated that condition to ensure that the other conditions are not causing the problems. I've also put similar logic into a similar sum(if(c,t,f)) to validate that the dates are performing as expected, and works fine there. I would rather not use that though for performance reasons.

Re: Set analysis before date + other conditions

sasiparupudi1 — Tue, 18 Aug 2015 14:29:12 GMT

Please check if you are getting the correct date format with the expression by putting it into a text box =Date(addyears(today(),-1))

it should be compatible to the date format in inv_DateValue

hth

Sasi

Re: Set analysis before date + other conditions

Tue, 18 Aug 2015 14:33:02 GMT

You may be onto something there - The inv_DateValue is in the datetime format rather than simply date.

addyears(today(),-1) = 18/08/2014

inv_DateValue = 18/08/2014 00:00:00

Re: Set analysis before date + other conditions

Tue, 18 Aug 2015 14:35:57 GMT

Though when I place a condition on a list box to pull through only dates older than a year, works fine.

My qlikview is broken!

Re: Set analysis before date + other conditions

sasiparupudi1 — Wed, 19 Aug 2015 07:54:18 GMT

try

=sum({$<

inv_YearValue={$(=year(today())-1)}

, ProductGroupName = {'Widgets'}

, inv_DateValue={"<=$(=Date(addyears(today(),-1)),'DD/MM/YYYY hh:mm:ss')"}

>}QTYINVOICED*ITEMQTY)

Re: Set analysis before date + other conditions

Wed, 19 Aug 2015 10:13:06 GMT

Thanks - definitely going the right direction, though it isn't actually filtering out any data. For example, the oldest inv_DateValue is in 2013, though if i change the last condition to even inv_DateValue={"<=$(=Date(addyears(today(),-10)),'DD/MM/YYYY hh:mm:ss')"}

... it still doesn't filter out any data.

Do I have a correct understand of set analysis here - should all conditions have to evaluate to true to get the value out?

Re: Set analysis before date + other conditions

sasiparupudi1 — Wed, 19 Aug 2015 10:17:38 GMT

your condition inv_YearValue={$(=year(today())-1)} is set to last year always

sum({1<

inv_YearValue=

, ProductGroupName = {'Widgets'}

, inv_DateValue={"<=$(=Date(addyears(today(),-1)),'DD/MM/YYYY hh:mm:ss')"}

>}QTYINVOICED*ITEMQTY)

hth

Sasi