topic Use if function in Sum in expression in QlikView

Use if function in Sum in expression

Mon, 07 Mar 2011 21:37:16 GMT

In a table i have the next values (among others of course),

Year, StartWeek, EndWeek

I want to calc the period in weeks between the start and and week. I have the next formule which works fine for records with the start en the end week in the same year.

SUM({<Source={Source1},Year={$ (=maxstring(Year))}>}[EndWeek] -[StartWeek]+1)
This one works fine.

When the start week is in one year and the end week in the other i get the next, asuming that one year is always 52 weeks

SUM({<Source={Source1},Year={$ (=maxstring(Year))}>}
If([EndWeek]-[StartWeek]<<0,
52-[StartWeek]+[EndWeek]+1,
[EndWeek]-[StartWeek]+1
)
)

But this one returns strange high values.

Can anyone give me a help please? Thnx in advance

Use if function in Sum in expression

Mon, 07 Mar 2011 22:29:13 GMT

I'm not 100% sure what the solution is but the problem is that your week numbers don't have years associated to them (the first week of every year is 1 instead of 1 and 53, etc.). I think you need to use the weekyear function on for the [StartWeek] and [EndWeek] fields in the script.

Use if function in Sum in expression

Mon, 07 Mar 2011 22:35:04 GMT

This could be a solution, but I also want to know how to solve it with the if function as I described

Use if function in Sum in expression

Mon, 07 Mar 2011 22:45:39 GMT

Ignore my last suggestion I was confused by the reference manual. I'm pretty sure that's still what you want to do but I'm not sure which function to use or anything like that.

Use if function in Sum in expression

Tue, 08 Mar 2011 09:40:52 GMT

Someone else a hint, I'm a bit stuck with this problem. Thnx in advance !

Use if function in Sum in expression

Miguel_Angel_Baeyens — Tue, 08 Mar 2011 10:27:37 GMT

Hello,

My guess is you have "Week" as dimension (field) with possible values from 1 to 52 (or 53). When the chart displays more than one year -say 2010 and 2011-, it accumulates the Week number, meaning that when you see at Week 2 the chart is aggregating values from week 2 in year 2011 and year 2010. So I'd create a new field in your master calendar like

WeekName(YourDateField) AS WeekName

Then the expressión should work without conditional, using this new field as dimension, instead of "Week".

Hope that helps.

AW:Re: Use if function in Sum in expression

Tue, 08 Mar 2011 10:40:18 GMT

Hello,

I am sure that you can't use your expression in the way you do. According to what Miguel said: If you do not differentiate your weeks (belonging to exactly one year) it won't work correctly. Examp:

- the result of your expression is for "05-2010" and "06-2011" the same as for "05-2010" and "06-2010". First should be 53!!

Regards, Roland