topic Chart based on Today() in QlikView

Chart based on Today()

Mon, 25 Jul 2011 17:05:38 GMT

Hi All

I want to create the chart which counts the ID based on the following date values Today()-14,Today-30,Today()-90 and YTD. I thought of doing it by passing these days values in the cyclic group. Is it the right way of doing it. I am little confused how to do this. Does any one have sample application which does the same thing? If you can attach it will be helpful for me.

Regards

Attitude

Chart based on Today()

swuehl — Mon, 25 Jul 2011 17:26:27 GMT

Hi,

If I understood correctly you might use set analysis for that:

YTD:

=count({<Date={">=$(=yearstart(today()))"}> ID}

Today-14 till today:

=count({<Date={">=$(=today()-14)"}> ID}

etc.

Use this as expression in a chart (just for comparison of these values, you don't need a dimension).

Is this something you intended?

Stefan

Re: Chart based on Today()

Tue, 26 Jul 2011 04:24:41 GMT

Hi Stefan

Thanks for your response! Still I haven't decided which will be the best way to develop this chart. I want to create the chart which is effective enough for the end user to analysis their data.

If you find free time you can give some suggestion on this.

I am welcoming all your suggestion. Please help me out!

Regards

Attitude

Re: Chart based on Today()

Tue, 26 Jul 2011 05:20:54 GMT

Hi Stefen

Just for your information. There is one small mistake in the expression which you have recommended for me.

YTD:
=count({<Date={">=$(=yearstart(today()))"}>} ID)
 
Today-14 till today:
=count({<Date={">=$(=today()-14)"}>} ID)

Regards

Attitude

Re: Chart based on Today()

swuehl — Tue, 26 Jul 2011 09:33:22 GMT

Yeah, needs to get my eyes checked!

I wrote the expressions on my way home without checking, sorry for that.

I may also misunderstood your original question, I now think you were asking about the way to present your data, not to calculate them..

You could probably use a cyclic group or container to switch between the different values, but I am not sure if this the best way if you are not really out of real estate.

Maybe I would just show them side by side in a table or bar chart, but the periods for which the values are calculated differ, so you can't really compare them one to another, so I would make it pretty clear, what the difference (i.e. period) is. If these values are known KPIs, showing them all side by side (or in distinct objects, side by side) would be ok, I think.

It could also be valuable if you would compare the distinct values to e.g. last years values.

Do you want to share a sample application?

Regards,

Stefan

Re: Chart based on Today()

Tue, 26 Jul 2011 09:58:29 GMT

Hi Stefen

I think instead of using <= we need to use >=, as we are count based on Today() to Today()-14. What do you say? I am little confused with this expression plese clear my doubt. Hope needs are clear are for you.

YTD:
=count({<Date={"<=$(=yearstart(today()))"}>} ID)
 
Today-14 till today:
=count({<Date={"<=$(=today()-14)"}>} ID)
etc.

Regards

Attitude

Re: Chart based on Today()

kaushiknsolanki — Tue, 26 Jul 2011 10:02:15 GMT

HI,

You should use the >= sign.

Let me explain how it works.

=count({<Date={">=$(=yearstart(today()))"}>} ID)

Here you are saying give me count of ID where the date is greater than the current yearstart.

Same way

=count({<Date={">=$(=today()-14)"}>} ID)

Here you are saying give me count of ID where the date is greater than the today() -14.

So here if today is 15-05-2011 it will give you 01-04-2011

Hope this will help you.

Regards,

Kaushik Solanki

Re: Chart based on Today()

swuehl — Tue, 26 Jul 2011 10:13:06 GMT

I don't think so, or have not understand your requirement:

above expression would translate using yearstart(today()) ---> 2011-01-01

to "count all IDs with Date <= 2011-01-01"

Similar, second expression would translate to "count all IDs with Date <= 2011-07-12

Regards,

Stefan

Re: Chart based on Today()

kaushiknsolanki — Tue, 26 Jul 2011 10:19:53 GMT

Hi,

Let me explain you again.

=count({<Date={">=$(=today()-14)"}>} ID)

As i said consider if today is 15-04-2011 then the function today() -14 will give you 01-04-2011

So accodring to the above expression you will get the count of ID's where the date is Greater than the 01-04-2011.

So the Id's whos date is lower then 01-04-2011. i.e for example 02-02-2011 will be ignored.

Now its up to you, that you want to cound the ID's which are greater then ot lesser then the date specified in expression i.e today()- 14.

Regards,

Kaushik Solanki

Re: Chart based on Today()

swuehl — Tue, 26 Jul 2011 10:34:30 GMT

Hi Kaushik,

my post was also related to at titudes question, not to yours, I have seen yours just after posting.

I think our both posts essentially agree.

Regards,

Stefan

Re: Chart based on Today()

Tue, 26 Jul 2011 16:48:41 GMT

Hi Stefen and Kaushik

I want to count all the ID's from Today()-14 and so as YTD(i,e Today's date to starting date of the year). I don't want only for the 14th day from today's date. I want to count for in between days also. Hope it is clear now.

Regards

Attitude

Re: Chart based on Today()

swuehl — Tue, 26 Jul 2011 16:51:04 GMT

Hi At titude,

I think it is clear, use >= .

Stefan

Re: Chart based on Today()

Tue, 26 Jul 2011 17:01:20 GMT

Thanks Stefen

Can you please explain hows does it work if you can. It will be helpful for me for better understanding.

As given I cannot calculate for YTD as our Year starts from 15-dec-YYYY. So may I know what changes are required for YTD expression.

Re: Chart based on Today()

swuehl — Tue, 26 Jul 2011 17:18:45 GMT

Sorry, I think I can't explain better than Kaushik and me already tried.

Please have a look in to chapter Set Analysis in the Help, or have a look here

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

Regarding your second question, that's kind of tricky but possible.

Unfortunately,

a) the usual QV functions that take an offset will only take a full month offset, not allowing start mid of month.

b) I have not more time to spend on this

Maybe like this:

=count({<Date={">=$(

=if( today()<makedate(year(today(),12,15), makedate(year(today())-1,12,15,),makedate(year(today(),12,15))

)"}>} ID)

Regards,

Stefan

Re: Chart based on Today()

Wed, 27 Jul 2011 04:54:01 GMT

Hi Stefen

I tried the solution which you have given it is not working out. When you find time please do help me out.

Regards

Attitude

Re: Chart based on Today()

Wed, 27 Jul 2011 05:10:06 GMT

Hi Stefen

Imagine if I use something like below one. May I know what would be the changes in the below code if I would like to calculate the minimum date of the year. For example, for the current year minimum date is 01/01/2011, I want to calculate minimum date of current year to Today(). So what will be the change in the below code. When you find time kindly look into it.

Sum(If([Date] >= Today()-14,1,0))

Regards

Attitude

Re: Chart based on Today()

swuehl — Wed, 27 Jul 2011 05:48:06 GMT

Please check my prev post. It seems I messed around with brackets at the makedate funktions.

You could reuse makedate(year(today()),1,1) as start of this year or yearstart(today()) in your

if Statement.

Re: Chart based on Today()

Wed, 27 Jul 2011 06:01:49 GMT

Hi Stefen

As I said you that our year starts from 15-dec-2010 not 01-jan-2011. So I don't think I can use YearStart directly. How you have understood what I need. Please help me out when you find time.

Regards

Attitude

Re: Chart based on Today()

swuehl — Wed, 27 Jul 2011 06:31:10 GMT

In your prev post you explicitely Named 2011-01-01 as year start .

For year start mid of dec recheck my yesterdays post anderen correct brackets.

I am travelling

Re: Chart based on Today()

Wed, 27 Jul 2011 06:41:14 GMT

Hi Stefen

Sorry if that confused you. I just said that for example. Also what you have provided is the set analysis. Try to give something which works with out set analysis as shown below.

Also for your information. 15-dec-2010 is not always the year start. For the year 2012 year start may be 16-dec-2011 and so on. Hope it is understandable.

As you are travelling I dont want to disturb you. You can reply me only when you find time. I am sorry to disturb you.

Regards

Attitude