Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. 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?

Highlighted
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),' #, ###. #% ')