topic Re: adding expressions with date calculations in QlikView

adding expressions with date calculations

Mon, 02 Dec 2013 09:24:10 GMT

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?

Re: adding expressions with date calculations

er_mohit — Mon, 02 Dec 2013 09:38:14 GMT

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

Re: adding expressions with date calculations

Mon, 02 Dec 2013 09:43:07 GMT

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

Re: adding expressions with date calculations

jagan — Mon, 02 Dec 2013 10:09:41 GMT

HI,

Can you attach some sample qlikview file?

Regards,

Jagan.

Re: adding expressions with date calculations

er_mohit — Mon, 02 Dec 2013 12:13:47 GMT

on Front End Side

in Variable Overview add Variable