1 Reply Latest reply: Mar 22, 2017 8:48 AM by Shiva Nagesh Bodepudi RSS

    Bar Chart to show last 6months data respective to monthyear selection

    Praveena Kumar

      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