Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
count(If(startDate <=MonthEnd And (endDate >= MonthStart Or Len(Trim(endDate )) = 0 ,id))
Could you include the document, I have a theory but need to see how you connect the calendar first.
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.
Here are my example files
I didn´t solved it yet.
Does anybody has an idea?
Can't you use Count(Sub_id)?
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
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?
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