topic Re: PLEASE HELP!!! in QlikView

PLEASE HELP!!!

Wed, 10 Oct 2012 13:51:29 GMT

Why oh why is this soooo difficult....Please can someone explain to me why this is not working:

=Sum({< [ Date] = {">=$vLastYearDay"} >} [Cart quantity])

I am trying to force a chart to give me yearly sales/quantities on a rolling twelve month basis starting on the current date so I would have something like:

2005 2006 2007 2008

50,352 48.214 75,654 85,920

BUT the current year would be today() and today()-365

last year would be today()-365 and today()-730

Pleas can someone help

Kind Regards,

Miles

Re: PLEASE HELP!!!

Wed, 10 Oct 2012 14:11:09 GMT

Hi,

Is it working when you write it like this:

Sum({< [ Date] = {">=$(=vLastYearDay)"} >} [Cart quantity])

If you want to show sales on a rolling twelve month basis starting today, you may create Calculated Dimension in a straight table:

if([Date]>date(addyears(Today(0),-1)),year(Today(0)),

if([Date]>date(addyears(Today(0),-2)),year(Today(0))-1,

if([Date]>date(addyears(Today(0),-3)),year(Today(0))-2,

'Before '&year(Today(0))-2)))

Then just sum your Sales in the expression.

Hope this helps,

Re: PLEASE HELP!!!

Wed, 10 Oct 2012 14:23:20 GMT

Thank you so much!!! borislav,

The expression did not work, but that calculated dimension is a dream...i am now glueing my hair back in!!

Thanks again,

Miles

Re: PLEASE HELP!!!

Thu, 11 Oct 2012 11:11:40 GMT

Glad I was of help.

I just want to mention that if you reload your application every night, then you can add the same dimension in your script, as a new CalculatedYear field to your calendar for example. Then you can use this field as a bona fide dimension in a straght table.

It is best to avoid calulated dimensions , if this is possible, because they slow down the app. response when selections are made.

cheers,