Hi All,
Am trying to show the last 6months data in bar chart in QlikSense. When I select a MonthAndYear value the bar chart should show last 6 months data from the point of selection. Example: If I select Feb2017 in MonthAndYear list box, then bar chart should show last 6 months from Feb2017.
By default it should show the last 6 months from the current month (date(max(MDate),'date format'))
I have created the fields like below in calendar,
date(date#(TempDate,'MM/DD/YYYY'),'MMM|DD|YYYY') as MDate,
Month(TempDate) AS Month,
Year(TempDate) AS Year,
MonthName(TempDate) as MonthAndYear
On UI, I have a list box for MonthAndYear and a Bar chart. Am using below expression in Bar chart with Dimension as MonthAndYear field.
((((sum({<[MID] = {6},MonthAndYear={">$(=date(addmonths(max(MonthAndYear),-6),'MMM YYYY')) <=$(=date(max(MonthAndYear),'MMM YYYY'))"}>}value))
*
(sum({<[MID] = {9},MonthAndYear={">$(=date(addmonths(max(MonthAndYear),-6),'MMM YYYY')) <=$(=date(max(MonthAndYear),'MMM YYYY'))"}>}value))
*
(sum({<[MID] = {8},MonthAndYear={">$(=date(addmonths(max(MonthAndYear),-6),'MMM YYYY')) <=$(=date(max(MonthAndYear),'MMM YYYY'))"}>}value))
*
(sum({<[M ID] = {7},MonthAndYear={">$(=date(addmonths(max(MonthAndYear),-6),'MMM YYYY')) <=$(=date(max(MonthAndYear),'MMM YYYY'))"}>}value)))* only({<CompositeMetric = {'DRI'}>} MWeight))
+
((sum({<[MID] = {3},MonthAndYear={">$(=date(addmonths(max(MonthAndYear),-6),'MMM YYYY')) <=$(=date(max(MonthAndYear),'MMM YYYY'))"}>}value))* only({<CMetric={'SCR'}>}MWeight)))
This above expression gives the error 'Selections generated no data for this chart'
The other way like below worked statically but cant show last 6months with respect to MonthAndYear selection. Rather it shows only for the selected MonthAndYear.
(((((sum({<[MID] = {6},[MDate]={">$(=date(addmonths(max([MDate]),-6),'MMM|DD|YYYY')) <=$(=date(max([MDate]),'MMM|DD|YYYY'))"}>}value))
*
(sum({<[MID] = {9},[MDate]={">$(=date(addmonths(max([MDate]),-6),'MMM|DD|YYYY')) <=$(=date(max([MDate]),'MMM|DD|YYYY'))"}>}value))
*
(sum({<[MID] = {8},[MDate]={">$(=date(addmonths(max([MDate]),-6),'MMM|DD|YYYY')) <=$(=date(max([MDate]),'MMM|DD|YYYY'))"}>}value))
*
(sum({<[MID] = {7},[MDate]={">$(=date(addmonths(max([MDate]),-6),'MMM|DD|YYYY')) <=$(=date(max([MDate]),'MMM|DD|YYYY'))"}>}value)))* only({<CMetric = {'DRI'}>} MWeight))
+
((sum({<[MID] = {3},[MDate]={">$(=date(addmonths(max([MDate]),-6),'MMM|DD|YYYY')) <=$(=date(max([MDate]),'MMM|DD|YYYY'))"}>}value))* only({<CMetric={'SCR'}>}MWeight)))
Please help me to solve this.
Thanks,
Praveen
