topic Re: Help with Sum and variables in QlikView

Help with Sum and variables

Thu, 01 Aug 2013 17:16:07 GMT

Hi,

I have the following sum which works:

=Num(sum({<[Year]={$(vCurrentYear)}>}[Hours Worked]*if(IsNull(Rate), 20, Rate)), '£#,##0;(£#,##0)')

What I can't figure out is to add the equivelent of an AND in.

=Num(Sum({<[Year]={$(vCurrentYear)}>} and If(Not([Project Status]='Admin'), [Hours Worked]*if(IsNull(Rate), 20, Rate))), '£#,##0;(£#,##0)')

I was thinking of something like the above, but that just gives a '-' in the result. Any help would be appreciated.

Thanks

Gysbert_Wassenaar — Thu, 01 Aug 2013 17:41:02 GMT

Try:

=Num(Sum({<[Year]={$(vCurrentYear)}, [Project Status] -={'Admin'}>} [Hours Worked]*if(IsNull(Rate), 20, Rate))), '£#,##0;(£#,##0)')

The expression editor doesn't know yet that -= is correct syntax, but that's a bug in the editor so don't worry about the red underlining.

Thu, 01 Aug 2013 17:50:10 GMT

Thanks Gysbert, pasted it in and it's showing '-', so that didn't work unfortunatly.

The editor didn't underlinethe -= , however, it did on the folling {

Gysbert_Wassenaar — Thu, 01 Aug 2013 17:56:50 GMT

Didn't count the '(' and ')' 's. Try:

=Num(Sum({<[Year]={$(vCurrentYear)}, [Project Status] -={'Admin'}>} [Hours Worked]*if(IsNull(Rate), 20, Rate)), '£#,##0;(£#,##0)')

Fri, 02 Aug 2013 07:57:57 GMT

Perfect, thank you