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
Creator III
Creator III

Bar Chart To Show From Previous Month Year Onwards

Qlikview 12 SR 4

I have a field called:   [DSBPrintExpiry Period]  (used as the bar chart dimension)

which has Month & Year in it in this format:-

Jan 2020

Feb 2020

Mar 2020

Apr 2020

May 2020

Jun 2020

Jul 2020

Aug 2020

Sep 2020

 

I want to show in a bar graph showing only from the previous period onwards.  So as we are in Jul 2020 now the chart would only display periods from Jun 2020 onwards and so forth each month.

 

So based on the list above the chart would show

Jun 2020

Jul 2020

Aug 2020

Sep 2020

 

I have tried this as the expression but it comes back with no data to display (I know there is data)

=Count({$<[DSBPrintExpiry Period]={">=$(Date(AddMonths(Today(),-1),'MMM YYYY'))"}>}[Key Join])

and from there I'm baffled as to why that doesn't work??

Any ideas much appreciated

 

I can't really post the app unfortunately

 

Subroutine (credit to Rob Wunderlich I believe) used to generate the [DSBPrintExpiry Period] field and others........

I call the below subroutine with:-

// Call Calendar Generator for field DSB Print Expiry Date
CALL CalendarFromField('DSB Print Expiry Date', 'DSBPrintExpiryCalendar', 'DSBPrintExpiry ');

SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
,month([$(_field)]) & ' ' & year([$(_field)]) as [$(_prefix)Period]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB

Labels (3)
2 Replies
Anonymous
Not applicable

not quite sure, but what might be is that your field [DSBPrintExpiry Period] is recognized as string
,month([$(_field)]) & ' ' & year([$(_field)]) as [$(_prefix)Period]

if you can define a datefield like date(makedate ,year([$(_field)]),month([$(_field)])),'MMM YYYY' ) as [$(_prefix)Period]
Don't know if the syntax is correct, but I would try it

By the way: if ou use today() without a parameter you get the date of the loaddate
using today(1) would give you the date when you call the funcion

Highlighted
Creator III
Creator III

Thanks for the reply.

So I ignored the Calendar subroutine completely and just constructed a MMM YYYY field in this way...

Date(MakeDate(Year([DSB Print Expiry Date]),Month([DSB Print Expiry Date])),'MMM YYYY') as [DSB Print Expiry Period]

And then used this in as my chart expression...

=Count({$<[DSB Print Expiry Period]={">=$(Date(AddMonths(Today(1),-1),'MMM YYYY'))"}>}[Key Join])

But it just displays every single MMM YYYY period available in the dataset (thanks for the Today(1) hint by the way)

 

The following works for displaying current year period onwards so I'm using that for now as I can't get the above to work

=Count({$<[DSBPrintExpiry Year]={">=$(=Year(Today(1)))"}>}[Key Join])

 

The dimension is:  [DSB Print Expiry Period]