How to check a previous date is in selection range

[#1]

I need to know at runtime that previous date in calendar is/isn't in selection range.

I've tried different approaches - no results:

sum + if = sum(if((dmNumDate - 1) = dmNumDate, 1, 0) )

count + set =count({$ <dmNumDate={$(=dmNumDate-1)}>} dmNumDate)

sum + set + DayCount =sum({$ <dmNumDate={$(=dmNumDate-1)}>} DayCount)

My expectation is:

for 10th - it should be 0.

for 11th, 12th, 13th and 14th - it should be 1.

I'll so appreciate for any suggestions.

[Oleg_Troyansky]

Nick,

this problem cannot be solved with Set Analysis... Set Analysis condition is evaluated "globally", i.e. outside of the Chart Dimensions. Therefore, you can't create a Set Analysis condition that's dependent on the current Dimension value (e.g. "Previous Date").

As much as I despise IF formulas, you'll have to use one of those...

Or, alternatively try to build the data structure that would solve your needs. For example, create a separate field "Previous Date" and create the "regular" chart based on the Previous Date that would include on exclude data based on database associations instead of Set Analysis.

[tabletuner]

Hi Nick,

Please have a look at the answer that Alex Erwin suggests in a similar problem in the following topic:

http://community.qlik.com/forums/p/25197/106864.aspx#106864

Good luck!

[#4]

Ok. The problem is much wider than YTD or PreviousDate calculation task when you can calculate data at a load script time. The problem is about to find continued sequence of dates at RUNTIME according to selection from different dimensions.

Let me describe the task. Everybody has the Internet, I hope 🙂 And now, let's pretend that you are an Internet Service Provider (ISP). And you need to know how many active sessions do you have daily, weekly, monthly and let's say from Monday through Wednesday of each second week of each third month. And this is simple when you already have a period for this. But the problem is an user wants to pick periods at Runtime.

You can say it's easy. All what you need It's just load all sessions by dates, assign them to a calender, add new column for each session like "SessionCount" and use sum(SessionCount). I did this and it works fine while you need just 1 month data. Because daily, it's about 20 millions records just for sessions. And you need to have some other dimensions, not just calendar. I need to load data for whole last year and make this statistics on hourly basis. I've simplified this post to daily data only.

So, as you can see, it's necessary to pre-aggregate the data. I can calculate number of sessions started (SS) for particular day. I can calculate number of entered sessions (ES) to the day from previous days. We have a formula then for active sessions (AS) for ONE day AS = ES + SS. Bat when you have a continued sequence of days then you have to use formula

AS = sum(SS) + ES for first day in a sequence.

And this is very simple to do when you have just one continued sequence. The formula will be

AS = sum(SS) + sum({<dmNumDate={$(=min(dmNumDate))}>} ES)

But in reality, it's something like "from Monday through Wednesday of each second week of each third month". As you can see from this example we may have many continued sequences of dates for each "third month" for each "second week" from Monday through Wednesday. For daily chart it's much "better"- each day is a separate sequence.

That's why I need to figure out how to find a first day of a sequence to retrieve ES for this day only. And it should be done by chart formulas at RUNTIME. There is nothing to do with load script. I'm thinking right now about macros, but I don't like an idea to run macros for each day in a selection.

May be after this explanation you will say - "Hey, it's too complicated. There is a standard way to do this ". And I will so appreciate if show me the way.

I've attached updated QV file with ES, SS and AS.

Thanks,

Nick

[#5 · Accepted solution]

Don't worry about this. I've already found a solution.

Thanks.