22 Replies Latest reply: Jul 27, 2011 5:19 AM by Stefan Wühl

# Chart based on Today()

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()

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()

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.

Regards

Attitude

• ###### Re: Chart based on Today()

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()

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()

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()

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

Regards,

Kaushik Solanki

• ###### Re: Chart based on Today()

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()

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()

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()

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()

Hi At titude,

I think it is clear, use >= .

Stefan

• ###### Re: Chart based on Today()

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()

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()

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()

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()

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()

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()

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()

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

• ###### Re: Chart based on Today()

I think instead of using today() in the below code I need to pass some date which gives the yearstart of my own calendar.  Got stuck here, don't know how to pass that.  Also not sure which date field should be passed here which gives the yearstart on my own calendar.

```Date(yearstart(today()))
```
• ###### Re: Chart based on Today()

Hi Stefen

The one which you have posted yesterday may not work as we have hardcode the year start there. Where as in my case yearstart keep changing. So if time permits kindly give some solution for me please.

Regards

Attitude

• ###### Re: Chart based on Today()

So how ist your yearstart defined? What is the rule behind? Is it lunar

based?