Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Folks,
I'm trying to build a table with 2 collumns: Current Month Sales | Past Month Sales.
Could you help me with the expression to create the Past Month Collumn ?
Thanks
Vinicius
HI
Check this
http://community.qlik.com/message/325601#325601
Hope it helps
HI ,
For Past Month try this code :
if(Month(Date)=month(AddMonths(Today(),-1)), Sales ) as PastMonthSales
//Yusuf
Hi,
Try this Expression for last month data-
=sum({<Year={$(=max(Year))},Month={$(=month(addmonths((max(Date)),-1)))}>}Sales)
Regards,
Neha
Hi, Vinicius
Create two variables
1. vCurrentMonth
=Max(Month)
and make ur expression like:
sum( {$<Year = {$(#vCurrentMonth)}>} Sales )
2. vLastMonth
=Max(Month-1)
sum( {$<Year = {$(#vLastMonth)}>} Sales )
Same as u can create Current Year and Last Year calculation
Regards
Ajeet Tomer
Hi,
Use =sum(Amount) for current month.
Use =sum({<Month={$(=Max(Month)-1)}>} Amount) for last Month.
it will work on your current selections:
Hope this help.
Kind Regards
Khan
Try
Current Month:
SUM({<DateField ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)
Previous Month:
SUM({<DateField ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)
In this Example if the DateField was [Calendar Date] it would look like this
SUM({<[Calendar Date] ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)
SUM({<[Calendar Date] ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)
Let me know if this works
Regards
Alan
I found the following to work more accurately for me.
=count ({<ClaimsAssisstantSubmittedDate ={">=$(=MonthStart(Today(),-1)) <$(=MonthStart(Today()))"}>} JobID)
The difference:
Alan's code has <= monthend(today,-1);
I have < monthstart(today)
Hi,
The easiest solution to this would be to the monthAgo calculation in the Qlik Calender.
create an auto calendar for the date field you are trying to visualize by using the Derive fields function in the calendar script. ( I can attach the complete auto calendar script if needed)
ex:- DERIVE FIELDS FROM FIELDS [date Field]
now, for current month the code in the measure field would be :
Sum( {$ < [Date.autoCalendar.MonthsAgo] = {'0'}>} sales)
U can change the 0 to 1,2,3 to go back to n last months.
Hope this helps.
could you please send me auto calendar full script?
thx
Murat