Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working on QlikSense and having one table with two columns Date and Stock. I just need to show barchart to show previous three months stock using stock date like current month month0, previous month month1 and rest all in month2.
StockDate Stock
08/02/2014 100
07/02/2014 200
08/02/2015 100
07/02/2015 100
06/02/2015 100
These value we need to change on Date selection also i.e if someone select Aug 2014 then current month is Aug 2014.
I am using Expression in dimension to make Month0, Month1 etc but facing problem to use same expression for measure (it's not returning correct data).
So can anyone help how to map measure part with dimension if we are making dimension using expression?
Many Thanks
If you define a variable in the script (or as of 2.1 in the UI) with one of the following:
script: set vMaxSelectedDate=date(max(StockDate));
UI: vMaxSelectedDate =date(max(StockDate))
This expression in a chart will filter the data to just the last 3 months
Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-2))))<=$(=date(monthend(vMaxSelectedDate)))"}>} Stock)
Attached is a sample with the data you provided
Thanks Sir but UI Doesn't Works.
Sir i need a help i m using max selected date for current month i want to calculate Stock For same current month till today date but last year how it is possible please help
Like Unallocated Stock for current month to till date
Previous month all stock
now i want to calculate for previous year current month till date
Same approach, just modify the date range in the Set Analysis modifier using the variable defined in the previous post:
Current Month to date
Sum( {<StockDate={">=$(=date(monthstart(vMaxSelectedDate)))<=$(=date(vMaxSelectedDate))"}>} Stock)
Last 3 months (up to today)
Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-2))))<=$(=date(vMaxSelectedDate))"}>} Stock)
Last 3 months (previous year)
Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-14))))<=$(=date(addmonths(vMaxSelectedDate,-12)))"}>} Stock)
Previous Month:
Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-1))))<=$(=date(monthend(addmonths(vMaxSelectedDate,-1))))"}>} Stock)
We need to filter on date also for previous year like as today's date is 22 sep, so we need to get data from 1st to 22nd for previous year..
set vMaxSelectedDate=date(max(StockDate));
SUM({<Year =, Month =, Week = , StockDate = {">=$(=MonthStart(AddYears($(vMaxSelectedDate),-1),0))<=$(=MonthEnd(AddYears($(vMaxSelectedDate),-1),0))"}>}(TotalUnAllocatedVal))
As of today's date is different to as of the user's max selected date. Replace vMaxSelectedDate with Today() function.
Previous year month to date (as of today) would be:
SUM({<Year =, Month =, Week = , StockDate = {">=$(=MonthStart(AddYears(today(),-13)))<=$(=MonthEnd(AddYears(today(),-13)))"}>}(TotalUnAllocatedVal))