adding expressions with date calculations

[#1]

Hi, I'm new to qlikview and would like to do something I think should be working but isn't.  I'm sure I'm missing some basic understanding of why this won't work, so any help is appreciated!

I have dimensions that are Event, Customer, Products Purchased.  I also would like to also allow further drilldown to what orders those products were purchased on as it may span several but isn't necessary if it's not possible. Then I have 1 expression with a calculation of 'sum(ExtSellingPrice)' which is the ExtendedPrice (sales $ of those Products Purchased).  That calculation works fine and has subtotals, etc.

I am now trying to add multiple calculated expressions that would then break the dollars out into 'buckets' within a date range.  So the next column would be ExtendedPrice of products purchased within 30 days of Event date (and then another for 60 days, etc).  I added an expression with a calculation of 'if(OrderDate <= EventStartDate + 30 and OrderDate >= EventStartDate, sum(ExtSellingPrice)) '  but I never get a return value, just a '-'.  I've tried adding date functions around each piece, but that doesn't seem to work.  Is something like this possible as it would be with other reporting tools or should I be doing it another way?

[er_mohit · Accepted solution]

make a variable for eventstartdate with number date format  as alike then compare it to OrderDate

like this

vEventstartDate = num(EventStartdate) and in expression side write

sum(if(num(OrderDate)<=((vEventstartDate) + 30) and  num(OrderDate) >= vEventstartDate, ExtSellingPrice))

hope it helps

[#3]

Thanks.  I'd like to try this.  Where do I define that variable?  In the load script somehow?

[jagan]

HI,

Can you attach some sample qlikview file?

Regards,

Jagan.

[er_mohit]

on Front End Side

in Variable Overview add Variable