Count ids with Date dimension and expressions

[#1]

Hello Community

I´m new to QlikView and i´m facing a Problem.

in SQL i have something like that:

SELECT COUNT(id) FROM Table s where s.startDate <= "2014-10-31" and (s.endDate >= 2014-10-01 or s.endDate is NULL)

In QlikView i tried something like that in expression:

count({<startDate ={"<=$(=MonthEnd)"},endDate={"NULL",">=$(=MonthStart)"}>}id)

Where "MonthStart" and "MonthEnd" are fields from a connected Calendar

When i filter by a special Date i get the right value for this Month. I get all values from the Table where startDate is less then the chosen Date(MonthStart) and endDate is higher than the chosen Date(MonthEnd)

I want to show a Chart with Dimension "Month".

for Oct  MonthsEnd = "2014-10-31" and MonthStart = "2014-10-01"

for Sep MonthsEnd = "2014-09-31" and MonthStart = "2014-09-01"

But what it does right now is when i make a full accumulation, for every dimension (oct, sep, aug...) it allways uses MonthsEnd = "2014-10-31" and MonthStart = "2014-10-01".

Please can somebody help me to achieve this problem.

Steve

[anbu1984]

count(If(startDate <=MonthEnd And (endDate >= MonthStart Or Len(Trim(endDate )) = 0 ,id))

[#3]

Could you include the document, I have a theory but need to see how you connect the calendar first.

[#4]

Let's say its a Table with Subscriptions

everey Subscription has a startDate and a EndDate(if the Subscription is Cancelled) otherwise EndDate is Null

Want i want to achieve is i want to show a chart with all the historical active Subscriptions for everey Month.

so Oct for example should count me all values where the startDate <= MonthEnd of Oct and endDate is null or endDate is >= MonthStart

--> because when Subscription was cancelled in month Oct, Subscription was still active in this month.

My Table is connected via the startDate field to CalenderTable.

[#5]

Here are my example files

[#6]

I didn´t solved it yet.

Does anybody has an idea?

[anbu1984]

Can't you use Count(Sub_id)?

[#8]

that counts me all values

but i want to count only values for given month with condition (endDate >= startMonth or endDate is Null)

and want i want is a chart with dimension month.

and for everey month it should count all ids  where startDate <= endMonth and (endDate >= startMonth or endDate is Null)

endMonth is the last day of month

startMonth is the first day of month

[anbu1984]

In your sample, endDate is always greater than startDate or endDate is null and startMonth is calculated based on startDate. Then all your endDate > Startmonth.

Can you show from your sample where endDate < startMonth?

Can you provide expected count for Nov-2014?

[#10]

this is the count for Nov-2014. This Solution works but i always have to chose the startDate. but what i want is a chart with dimension Month