topic Is this possible? in QlikView

Is this possible?

samvile18 — Tue, 19 Jul 2011 12:07:53 GMT

Hi All,

I'm fairly sure what I'm trying to do is possible I just don't quite know the syntax!

I have a table which holds an upload date for the latest instance a KPI has been uploaded. What I need to do is make sure both graphs only show data which matches the latest upload date.

The route I was trying to take was through setting up a variable but I can't quite figure it out...any help is greatly appreciated.

I've attached a dummy QVW for referance.

Is this possible?

Tue, 19 Jul 2011 12:49:35 GMT

Hi Sam,

Try something like this in Expression

Sum(if([Measure]='FUM' and KPI = 'FUM' and UploadDate = ExtractDate,[Value],0)) and it should work.Presently you do not have data in the existing application for June 2011. ----------- Talha

Is this possible?

samvile18 — Tue, 19 Jul 2011 13:13:40 GMT

Excellent....I didn't think of a nested IF statement...thanks for the help.

Is this possible?

samvile18 — Wed, 20 Jul 2011 09:30:57 GMT

While this solution works it also takes a long time to calculate...does anyone know of a quicker (more system friendly) way of doing this?

Re: Is this possible?

johnw — Thu, 21 Jul 2011 19:56:21 GMT

It's difficult to test since you don't have any data that matches your upload dates, but you should be able to use set analysis to do the same thing as the if() but with higher performance. Perhaps something like this:

sum({<Measure={'FUM'},ExtractDate={'$(=date(max({<KPI={'FUM'}>} UploadDate)))'}>} Value)

Re: Is this possible?

samvile18 — Fri, 22 Jul 2011 08:30:53 GMT

Thats a good point...I've attached another version of the test QVW as I can't get the set analysis to work.

Is it worth stating that I need to the formula to work using the month and year of the upload date...there is a field called UploadMthYr which is basically a concatenation. I think I need that to link to the MonthYear field within the MasterCalendar

Thanks for the help John, I really appreciate it.

Is this possible?

samvile18 — Mon, 25 Jul 2011 09:17:39 GMT

**BUMP**