Set analysis before date + other conditions

[#1]

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!

[jonathandienst]

I think that should be :

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

[jonathandienst]

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

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

[sasiparupudi1]

=sum({$<

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

     , ProductGroupName = {'Widgets'}

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

>}QTYINVOICED*ITEMQTY)

[maxgro]

=sum(

     {<

          ProductGroupName = {"Widgets"},

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

     >} QTYINVOICED*ITEMQTY)

[#6]

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.

[sasiparupudi1]

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

[#8]

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

[#9]

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!

[sasiparupudi1]

try

=sum({$<

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

     , ProductGroupName = {'Widgets'}

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

>}QTYINVOICED*ITEMQTY)