topic Re: Display Current month and rolling month data in QlikView

Display Current month and rolling month data

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

Hi,

I'm trying to display Jan- Dec for each year but because this is too long I would like to display it in quarterly months.So it all fits on one page.

Also on the same page I want to display the current month too. I want to use this eventually on nprinting.

I have tried different set analysis and expressions but still not getting what I want, help would be appreciated

examples used:- =Sum({$<Month= {"$(=Month(Today()-1))"}>}qty)

If ( Creationdate, = Month(Addmonths(Today(),-1)),'Y','N') all flags return 'N'

Re: Display Current month and rolling month data

Kushal_Chawda — Tue, 25 Jul 2017 09:08:02 GMT

is there a typo, try below

If ( Creationdate >= monthstart(Addmonths(Today(),-1)) and Creationdate <=monthend(Addmonths(Today(),-1)),'Y','N')

Re: Display Current month and rolling month data

mangalsk — Tue, 25 Jul 2017 09:26:34 GMT

Hello,

You have a field called Month so you can write below, you are comparing date with month which is not possible

if(Month=MonthName(Today()),,'Y','N') all flags

Re: Display Current month and rolling month data

qlikmpate0 — Tue, 25 Jul 2017 09:42:23 GMT

Hi Kushal,

This doesn't give me any rows with the value 'Y' which are in the previous month. all the rows are 'N'

Re: Display Current month and rolling month data

qlikmpate0 — Tue, 25 Jul 2017 09:43:18 GMT

I have tried it with month too but still not good

Re: Display Current month and rolling month data

Kushal_Chawda — Tue, 25 Jul 2017 09:46:57 GMT

check the Date format, may be it's in text, you need to first convert it in number

If ( date(date#(Creationdate,'DD/MM/YYYY')) >= monthstart(Addmonths(Today(),-1)) and date(date#(Creationdate,'DD/MM/YYYY')) <=monthend(Addmonths(Today(),-1)),'Y','N')

Assuming that your Date format is 'DD/MM/YYYY'. change the format as per the data

Re: Display Current month and rolling month data

mangalsk — Tue, 25 Jul 2017 10:11:49 GMT

Please see attached it is working , i have done in text box for reference

Re: Display Current month and rolling month data

qlikmpate0 — Tue, 25 Jul 2017 10:38:30 GMT

Ok the format of date was one of the issues. thank you so much for your help, but how can I display the quarterly months eg Jan-Mar, Apr-Jun, Jul-Sep,& Oct-Dec?

Re: Display Current month and rolling month data

qlikmpate0 — Tue, 25 Jul 2017 10:49:41 GMT

Thank you Mangal, I also need the Quarterly months to be displayed

Re: Display Current month and rolling month data

mangalsk — Tue, 25 Jul 2017 11:27:23 GMT

Dear Mina,

Please create calendar so that it will be easy for you to calculate as per your need on different views.

Currently you can do if( num(Month(CreationDate))>=1 and num(Month(CreationDate))<=3,'Q1',

if( num(Month(CreationDate))>=4 and num(Month(CreationDate))<=6,'Q2',

if( num(Month(CreationDate))>=7 and num(Month(CreationDate))<=9,'Q3',

if( num(Month(CreationDate))>=10 and num(Month(CreationDate))<=12,'Q4')))) as Quarter or as per your need please change months

Re: Display Current month and rolling month data

Kushal_Chawda — Tue, 25 Jul 2017 11:34:08 GMT

create the calendar script like below

MinMax:

LOAD min(Date) as MinDate,

max(Date) as MaxDate;

LOAD FieldValue('Creationdate',RecNo()) as Date

AutoGenerate FieldValueCount('Date');

let vMinDate = Peek('MinDate',0,'MinMax');

let vMaxDate = Peek('MaxDate',0,'MinMax');

Calendar:

LOAD *,

Year(Creationdate) as Year,

MonthName(Creationdate) as MonthYear,

QuarterName(Creationdate) as QuarterYear;

LOAD date($(vMinDate) +IterNo()-1) as Creationdate

AutoGenerate 1

While $(vMinDate) +IterNo()-1 <= $(vMaxDate) ;

DROP Table MinMax;

Now you can use QuarterYear as your required month format

Re: Display Current month and rolling month data

qlikmpate0 — Wed, 26 Jul 2017 09:13:44 GMT

if I'm displaying the current month, can I have YTD totals at the bottom, for example I have data for Jan-Jun.

I display Jun sales in summary table, but I want to also display total sales YTD on a separate line as shown below:

how can I do that with set analysis?