Is this possible?

[samvile18]

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.

[#2 · Accepted solution]

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

[samvile18]

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

[samvile18]

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?

[johnw]

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)

[samvile18]

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.

[samvile18]

**BUMP**