Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Show Previous Month for all Years

So I'm trying to build a bar chart that represents the following...

     Previous Month (Example - September)

Sept - 2018 --------------

Sept- 2017 ---------------------------

Sept - 2016------------------

How do a I show the previous Month (Sept) for each year if I have data through Oct 2018?

My date field is SurveyDTS.

My dimension is YearNBR.

My expression = Num(Round(Count({<TopBoxFLG = {'1'}>} SurveyID)/Count({<TopBoxFLG = {'1', '0'}>}SurveyID,.001),'#,###.#%')

7 Replies
Highlighted
Creator III
Creator III

Try used the extenssion "Default Seleccion"

https://github.com/fadyheiba/DefaultSelections

Highlighted
Partner
Partner

I am using Qlikview.

Highlighted
Anonymous
Not applicable

you may define additional set Analysis for your date field

YearNBR={'*Sept*'} if your date Format corresponds like Sept 2018 or 01. Sept 2018'

if not adapt the set Analysis to your requirement

expression = Num(Round(Count({<YearNBR={'*Sept*'},TopBoxFLG = {'1'}>} SurveyID)/Count({<YearNBR={'*Sept*'},TopBoxFLG = {'1', '0'}>}SurveyID,.001),'#,###.#%')

similiar Chart (data spans complet years)

Highlighted
Partner
Partner

This is what I'm looking for thank you! 

What do I do if my data is constantly changing?  More months will be added and I would rather not have the manual process of going in every month and adjusting the *Sept*.

Any thoughts?

Highlighted
Anonymous
Not applicable

you may use a variable for choosing the month

can you specify your requirement more in Detail?

Partner
Partner

I can definitely use a variable but I'm not that familiar with how to use them inside a set analysis. 

My requirement is exactly what you showed in your bar chart except it will always be the previous month of the current data.

Example:

Previous Month = Sep 2018

Current Month = Oct 2018

So we would want to show

Sep 2018

Sep 2017

Sep 2016

Highlighted
Creator III
Creator III

Try this

let vPriorMonth  = '=month(addmonths(max(YearNBR ),-1))';

Num (Round (Count ({<YearNBR = {' vPriorMonth '}}, TopBoxFLG = {'1'}>} SurveyID) / Count ({<YearNBR = {'vPriorMonth '}, TopBoxFLG = {' 1 ',' 0 '}>} SurveyID, .001),' #, ###. #% ')