Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

samvile18
Contributor II

Is this possible?

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.

1 Solution

Accepted Solutions
Not applicable

Is this possible?

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

View solution in original post

6 Replies
Not applicable

Is this possible?

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

View solution in original post

samvile18
Contributor II

Is this possible?

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

samvile18
Contributor II

Is this possible?

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?

MVP
MVP

Re: Is this possible?

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)

Highlighted
samvile18
Contributor II

Re: Is this possible?

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
Contributor II

Is this possible?

**BUMP**