Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)

Thanks in advance!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

10 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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!

Miguel_Angel_Baeyens

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

Not applicable
Author

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

Not applicable
Author

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)

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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?

Miguel_Angel_Baeyens

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