10 Replies Latest reply: Oct 16, 2017 1:07 PM by Gysbert Wassenaar RSS

    trying to filter dates on a chart with if..then.. logic from button selection result

    Todd Ohme

      I have an interface where I am showing trends in sales data.  so I'd like to be able to show

      'this week vs last week',

      '4weeks vs previous 4 weeks',

      '8weeks vs pre 8 weeks', and

      '13 weeks vs pre 13 weeks'

       

      I downloaded a multiple button widget from Qlik branch and implemented to allow a user to select from 1wk, 4wj, 8wk, 13wk to drive what is displayed in a table....and also a chart.   can use an if...then statement just fine in the table to drive the calculations, but I'm having a hard time getting a line chart to respond how I would like. 

      If the user has selected 1wk I'd like to show the last 4weeks,

      if the user selects 4wk, I would like to display the last 8 weeks in the chart only

      If the user select 8wks, I'd like to show the last 16 weeks,

      and if the user selects 13weeks, I'd like to show the last 26 weeks so the user can see a period over period comparison.

       

      I've pasted the script I'm trying to use on my sales units plot below.  If I hard code and 'if' result independently, the chart works as planned, only showing that range, but if I include them all in an if...then statement, the chart always shows the max number of dates that could be selected from the if...then results......and the values outside the values in the selected range, are all 0.  for example, if a user selects '4wk' (v_Period = '2') the chart should show 8 weeks, but with my script, the chart will show weeks 1 to 33 (the current vMaxWeek) and   weeks 1 thru 25 are 0 and weeks 26 thru 33 show the actual value.  My intent would be for only weeks 26 thru 33 to display in the chart for this selection.  if 8wk is selected (v_Period = '3') my intent would be for weeks 18 thru 33 to show in the chart.   Please let me know any recommendations and if another technique or adjustment could produce the desired results.  Thank you!

       

      v_Period is the variable returned from the multiple button Qlik branch widget.  value ranges from 1 to 5

      Fiscal Year and Fiscal Week are date fields in my fact table

      vMax Year and vMaxWeek are variables returning the max week and max year (current week and year) values from my load script....for the case of my example year of 2016 and week of 33

       

      if(v_Period = '1',

         Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-3)<=$(=vMaxWeek)"}>} [POS Units]),

         if(v_Period = '2',

            Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-7)<=$(=vMaxWeek)"}>} [POS Units]),

            if(v_Period = '3',

               Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-15)<=$(=vMaxWeek)"}>} [POS Units]),

               if(v_Period = '4',

                  Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-25)<=$(=vMaxWeek)"}>} [POS Units]),

              

                  Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={"<=$(=vMaxWeek)"}>} [POS Units])

                 )

               )

             )

          )

       

      Also....to simplify the issue, this simple if...then produces the same problem

      if(v_Period = '1',

         Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-3)<=$(=vMaxWeek)"}>} [POS Units]),

         Sum({$<[Fiscal Year]={$(=vMaxYear)}, [Fiscal Week]={">=$(=vMaxWeek-7)<=$(=vMaxWeek)"}>} [POS Units]),

          )