topic Re: Previous month compared to this month till todays date in QlikView

Previous month compared to this month till todays date

Mon, 08 Oct 2012 09:55:40 GMT

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)

Thanks in advance!

Re: Previous month compared to this month till todays date

Miguel_Angel_Baeyens — Mon, 08 Oct 2012 09:58:39 GMT

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

Mon, 08 Oct 2012 10:09:02 GMT

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

Miguel_Angel_Baeyens — Mon, 08 Oct 2012 10:32:05 GMT

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

Mon, 08 Oct 2012 11:18:45 GMT

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

Mon, 08 Oct 2012 11:23:48 GMT

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

jonathandienst — Mon, 08 Oct 2012 11:48:46 GMT

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

jonathandienst — Mon, 08 Oct 2012 11:51:29 GMT

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

Mon, 08 Oct 2012 11:57:01 GMT

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?

Re: Previous month compared to this month till todays date

Miguel_Angel_Baeyens — Mon, 08 Oct 2012 12:38:14 GMT

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

Mon, 08 Oct 2012 13:50:55 GMT

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!