Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

if expression is not working

express in stright table is not returning values. can you look at it and let me know what went wrong in my express.

Thanks for your help.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I got the requested values using this expression, please see attached.

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe like

=aggr(

if(Timestamp(FV_Date,'MM/DD/YYYY') =Timestamp(max(total<Make,Sub_Version> FV_Date),'MM/DD/YYYY') and Sub_Version = Max(total<Make> Sub_Version), Dev_Count)

,Make,Sub_Version,FV_Date)

Not applicable
Author

did not work.

swuehl
MVP
MVP

I got the requested values using this expression, please see attached.

Not applicable
Author

Sorry man. I was looking at wrong expression for the results. You are correct. For my knowledge, what was wrong with my if statement. For this scenario what would be the ideal solution ie. instead of writing  a long expression. Is there any Set Analysis that will help or any data model change would require.

Thanks for your help.

swuehl
MVP
MVP

Your original expression was

=if(Timestamp(FV_Date,'MM/DD/YYYY') =Max(Timestamp(FV_Date,'MM/DD/YYYY')) and Sub_Version = Max(Sub_Version), Dev_Count)

The reason why this can't work is that FV_Date and Sub_Version are not unambiguous per Make dimension value. You can see this when adding these as dimensions, there are lot of different values, so QV can't resolve above expression to one single answer.

You can slightly simplify my above suggested expression by removing the timestamp() formatting:

=aggr(if(FV_Date =max(total<Make,Sub_Version> FV_Date) and Sub_Version = Max(total<Make> Sub_Version), Dev_Count),Make, Sub_Version,FV_Date)

If you can extend your table in the data model by one more field:

Sub_Version + FV_Date/100000 as Test

you may be able to use FirstSortedValue() in your chart expression (if I understood your issue correctly), dramatically simplifying your expression to

=FirstSortedValue(Dev_Count, -Test)

Regards,

Stefan

Not applicable
Author

Thanks for your detail explination. If you look at data below, how do i sum for all Sub_version for a given make at 12/3/2012

Sub_VersionMake11/5/201211/12/201211/19/201211/26/201212/3/2012
2Congress53,82951,54650,48749,56648,701
3Congress59,51144,04542,92041,95541,235
4Congress16,39515,89815,66415,56415,426
5Congress           240,152                   265,369                         268,924               273,312               276,999
1Congress20,89618,12620,66922,33223,451
Total 390,783394,984398,664402,729405,812
swuehl
MVP
MVP

Maybe like this:

=sum({<FV_Date = {'12/3/2012'}>} Dev_Count)