Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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]),

    )

1 Solution

Accepted Solutions
Gysbert_Wassenaar

I didn't have your button extension so I replaced it with a simple table based menu. But it'll give you an idea of how to tackle this.


talk is cheap, supply exceeds demand

View solution in original post

11 Replies
Gysbert_Wassenaar

Can you post a small qlik sense app that illustrates the problem?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

absolutely.  this was a good exercise  as well to simplify the app and ensure it wasn't related to the complexity of my data.  Here is a very simple app where I loaded around 20 data points and plot them in a chart and then used the variable and the Qlik branch multiple button project/widget to control the variable v_Period....I'm guessing you can set the variable manually the app, or another way, but it looks like having two outcomes for the chart using set analysis is driving my issue with the way I am using set analysis and if...then.

GitHub - AssureeServices/MULTIPLE-BUTTONS: Set a variable in Qliksense and create customized multipl...

Please let me know if the problem I am trying to solve is more clear now.  Thank you for the quick response too!

!

Gysbert_Wassenaar

I didn't have your button extension so I replaced it with a simple table based menu. But it'll give you an idea of how to tackle this.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Well...i think I figured out my problem....I had 'show zeros' on in the data handling properties of the chart.  I researched this Pick solution and see where it's a lot less memory intensive than using if..then statements so I will be using this wherever possible now.  Brilliant.  Thank you for helping me get to a great solution!!

prananellutla
Creator
Creator

Hi - can we apply multi select on this filter? I have measures as a filter.

Gysbert_Wassenaar

Nope, one measure can show the result of only one expression at a time, not multiple.


talk is cheap, supply exceeds demand
prananellutla
Creator
Creator

Thankyou - I found a qvf example from 2015 post- I tried posting my qn but no response so far. I think the user is no longer active . In the attached QVF , we see multiple measures. I did the exact same thing. but it does'nt work for me. If you can take a look and help me understand ?

thanks

Prana

Gysbert_Wassenaar

The example app you posted contains one bar chart that has only one measure. You can use the buttons to change which expression the measure calculates. But just as in the example I posted only one expression is calculated at a time. It's not possible to show both sum of population and sum of sales at the same time.


talk is cheap, supply exceeds demand
prananellutla
Creator
Creator

Apologies- I have a wrong file uploaded

I meant to attach this one!