Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count ids with Date dimension and expressions

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

11 Replies
anbu1984
Honored Contributor III

Re: Count ids with Date dimension and expressions

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

Not applicable

Re: Count ids with Date dimension and expressions

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

Not applicable

Re: Count ids with Date dimension and expressions

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.

Not applicable

Re: Re: Count ids with Date dimension and expressions

Here are my example files

Not applicable

Re: Count ids with Date dimension and expressions

I didn´t solved it yet.

Does anybody has an idea?

anbu1984
Honored Contributor III

Re: Count ids with Date dimension and expressions

Can't you use Count(Sub_id)?

Not applicable

Re: Count ids with Date dimension and expressions

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
Honored Contributor III

Re: Count ids with Date dimension and expressions

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?

Not applicable

Re: Count ids with Date dimension and expressions

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 MonthsubscriptionCount.jpg

Community Browser