topic Show Previous Month for all Years in QlikView

Show Previous Month for all Years

kaylaramsey2 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Show Previous Month for all Years

MarioCenteno — Tue, 06 Nov 2018 15:51:49 GMT

Try used the extenssion "Default Seleccion"

https://github.com/fadyheiba/DefaultSelections

Re: Show Previous Month for all Years

kaylaramsey2 — Tue, 06 Nov 2018 15:56:43 GMT

I am using Qlikview.

Re: Show Previous Month for all Years

Anonymous — Tue, 06 Nov 2018 16:14:12 GMT

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)

Re: Show Previous Month for all Years

kaylaramsey2 — Tue, 06 Nov 2018 16:25:32 GMT

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?

Re: Show Previous Month for all Years

Anonymous — Tue, 06 Nov 2018 16:27:36 GMT

you may use a variable for choosing the month

can you specify your requirement more in Detail?

Re: Show Previous Month for all Years

kaylaramsey2 — Tue, 06 Nov 2018 16:33:52 GMT

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

Re: Show Previous Month for all Years

MarioCenteno — Tue, 06 Nov 2018 17:11:11 GMT

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