10 Replies Latest reply: Oct 8, 2012 9:50 AM by thijmenbosman

# Previous month compared to this month till todays date

Dear members,

For my qlikview file i am looking for an expression which can visualise my growth rate by defiding this months sales till today (for instance 8 okt) by last months sales till todays day. (so for instance (sales from 01-october till today)/(sales from 01-sept till todays day(8)).

I can't seem to figure it out!  =sum({<.........................>} sales)

• ###### Re: Previous month compared to this month till todays date

Hi Thijmen,

Although it's best practice to create this kind of fields in the script, the following should work for previous month to date:

```Sum({< Date = {"=InMonthToDate(Date, Today(), -1)"} >} Sales)
```

Hope that helps.

Miguel

• ###### Re: Previous month compared to this month till todays date

Thanks, the expression seems to work, but i do not get the answer i want.

I used your expression like this

`=Sum({< Date = {"=InMonthToDate(Date, Today(), 0)"} >} Sales)/Sum({< Date = {"=InMonthToDate(Date, Today(), -1)"} >} Sales)`

`hoping to defide this month till date by last month. It returns exactly 1.000 which is allmost impossible!`

`Did i do wrong changing the -1 to 0?`

`Thank you!`

• ###### Re: Previous month compared to this month till todays date

Yep,

It has been my fault, sorry. Check the following expression that is shown in the attached application:

```Sum({< DateField = {"=InMonthToDate(DateField, Today(), 0)"} >} Sales) // current month
/
Sum({< DateField = {"=InMonthToDate(DateField, AddYears(Today(), -1), 0)"} >} Sales) // LY month to date
```

Hope that helps.

Miguel

• ###### Re: Previous month compared to this month till todays date

Ok thank you,

but i see that this expression uses AddYears, can i just change it to AddMonths?

I mean to see the difference in months, in other words, how much more sales do i have this month compared to the last month. Since the data is useless it has to be for instance september till the 8th compared to october till the 8th.

Kind regards

• ###### Re: Previous month compared to this month till todays date

i used this expression to try it, but it doesn't seem to work. (no data displayed in a gauge chart)

Sum

({< date = {"=InMonthToDate(date, Today(), 0)"} >} cdr_totalamount_ex_vat) / Sum({<date = {"=InMonthToDate(date, AddMonths(Today(), -1), 0)"} >} cdr_totalamount_ex_vat)

• ###### Re: Previous month compared to this month till todays date

Thijmen,

That function was to compare with previous year but the same month. Use this instead:

```Sum({< DateField = {"=InMonthToDate(DateField, Today(), -1)"} >} Sales)
/
Sum({< DateField = {"=InMonthToDate(DateField, Today(), 0)"} >} Sales)
```

Hope that helps.

Miguel

• ###### Re: Previous month compared to this month till todays date

It seems that it was not working because the automated process of retrieving data stopped in septembre, therefore there was no data in october!

The last expression you send was correct and shall work when the data set is complete!

Thank you very much!

• ###### Re: Previous month compared to this month till todays date

Hi

Are there any other fields linked to date (such as month, year etc)? You may need to exclude them in the set expression, like this:

=Sum({< Date = {"=InMonthToDate(Date, Today(), 0)"}, year=, month= >} Sales) / Sum({<Date = {"=InMonthToDate(Date, Today(), -1)"}, year=, month= >} Sales)

Rename as required. Include any other fields linked to date.

Hope that helps

Jonathan

• ###### Re: Previous month compared to this month till todays date

Is the field Date or date? QV field names are case sensitive, if you use the wrong fieldname (ie incorrect capitalisation), then QV will ignore the set expression and you will be computing Sum(Sales)/Sum(Sales) = 1.

Regards

Jonathan

• ###### Re: Previous month compared to this month till todays date

I cannot use the 'year=' and month= in my set expression right? because when i do the expression will be wrong next month.

I do not totally get bot of your answers. Why are there two parts with inmonthtodate? One part of the expression should just be "sum(sales)" right, as there is no data after today so it does not have to be defined. Now i just need "sum(sales) previous month to todays day number" so how do you define the time set as

month=previous, day=todays day number.

I thought this was not a difficult thing to do but in qlikview appearantly it is?