Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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!
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
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
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)
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
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
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?
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