topic Re: if expression is not working in QlikView

if expression is not working

Fri, 07 Dec 2012 17:58:23 GMT

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.

Re: if expression is not working

swuehl — Fri, 07 Dec 2012 18:26:51 GMT

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)

Re: if expression is not working

Fri, 07 Dec 2012 18:53:43 GMT

did not work.

Re: if expression is not working

swuehl — Fri, 07 Dec 2012 19:01:10 GMT

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

Re: if expression is not working

Fri, 07 Dec 2012 19:19:20 GMT

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.

Re: if expression is not working

swuehl — Fri, 07 Dec 2012 21:39:19 GMT

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

Re: if expression is not working

Sat, 08 Dec 2012 03:56:18 GMT

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_Version	Make	11/5/2012	11/12/2012	11/19/2012	11/26/2012	12/3/2012
2	Congress	53,829	51,546	50,487	49,566	48,701
3	Congress	59,511	44,045	42,920	41,955	41,235
4	Congress	16,395	15,898	15,664	15,564	15,426
5	Congress	240,152	265,369	268,924	273,312	276,999
1	Congress	20,896	18,126	20,669	22,332	23,451
Total		390,783	394,984	398,664	402,729	405,812

Re: if expression is not working

swuehl — Sat, 08 Dec 2012 12:52:18 GMT

Maybe like this:

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