topic Last Quarter Data Population in QlikView

Last Quarter Data Population

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

I have a data in which i want the calculated field which has to show the current quarter information.

I have looked lots of post in qlik community but nothing is working out for me.Please suggest

Re: Last Quarter Data Population

shraddha_g — Wed, 08 Nov 2017 08:56:10 GMT

what is your data?

what is the expected output according to your data..

Please elaborate

Re: Last Quarter Data Population

vikasdesai — Wed, 08 Nov 2017 09:29:04 GMT

try this in your script Date/calendar table.

'Q' & Ceil(Month([DateField])/3) As Quarter,

Regard

Vikas

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 09:34:38 GMT

I have a column name called Process Date,Standard organization name & RTA. The process date column contain the entire year information.but i want to show case only the current quarter summation of RTA in a separate column in my final output page.@@@

Note : In each and every quarter the calculated column has to show only the current quarter information

Processed Date	Standard Organization Name	RTA
3/28/2017	FIDELITY INVESTMENTS	$2.00
1/12/2017	CHARLES SCHWAB	$3.00
10/31/2017	COMPETITION - IBG	$4.00
7/19/2017	MORGAN STANLEY DEAN WITTER	$5.00
5/1/2017	VANGUARD	$6.00
4/18/2017	MORGAN STANLEY DEAN WITTER	$7.00
9/28/2017	MORGAN STANLEY DEAN WITTER	$8.00
7/12/2017	MERRILL LYNCH	$9.00
1/20/2017	FIDELITY INVESTMENTS	$10.00
1/17/2017	FIDELITY INVESTMENTS	$11.00
3/27/2017	FIDELITY INVESTMENTS	$12.00
1/30/2017	FIDELITY INVESTMENTS	$13.00
2/9/2017	FIDELITY INVESTMENTS	$14.00
5/30/2017	MORGAN STANLEY DEAN WITTER	$15.00
2/21/2017	MORGAN STANLEY DEAN WITTER	$16.00
7/6/2017	UBS FINANCIAL SERVICES	$17.00
4/19/2017	CHARLES SCHWAB	$18.00
4/7/2017	FIDELITY INVESTMENTS	$19.00
6/5/2017	MORGAN STANLEY DEAN WITTER	$20.00
3/24/2017	CHARLES SCHWAB	$21.00
9/18/2017	NEW EMPLOYER DC/DB PLAN	$22.00
8/9/2017	BANK OF NEW YORK	$23.00
10/24/2017	FIDELITY INVESTMENTS	$24.00
2/13/2017	LINSCO PRIVATE LEDGER	$25.00
9/6/2017	EDWARD D. JONES	$26.00
2/13/2017	UNKNOWN	$27.00
5/3/2017	UNKNOWN	$28.00
2/8/2017	FIDELITY INVESTMENTS	$29.00
6/26/2017	MORGAN STANLEY DEAN WITTER	$30.00
10/2/2017	MERRILL LYNCH	$31.00
7/18/2017	UBS FINANCIAL SERVICES	$32.00
7/7/2017	MORGAN STANLEY DEAN WITTER	$33.00
7/10/2017	CHARLES SCHWAB	$34.00
2/14/2017	FIDELITY INVESTMENTS	$35.00
10/25/2017	THRIVENT	$36.00
10/11/2017	FIDELITY INVESTMENTS	$37.00
5/16/2017	AMERITRADE	$38.00
2/13/2017	BANK OF AMERICA	$39.00
3/21/2017	FIDELITY INVESTMENTS	$40.00

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 09:37:43 GMT

do i have to run this script in the data loader?

Re: Last Quarter Data Population

shraddha_g — Wed, 08 Nov 2017 09:37:59 GMT

Create QuarterStart in your Calendar Script as QuarterStart(DateField) as QuarterStart.

In Chart,

Use sum({<QuarterStart = {"$(=max(QuarterStart))"}>}RTA)

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 09:45:43 GMT

I have created a quarterstart variable but that is not working...let me try create a script

Re: Last Quarter Data Population

shraddha_g — Wed, 08 Nov 2017 09:57:59 GMT

PFA

Re: Last Quarter Data Population

HirisH_V7 — Wed, 08 Nov 2017 10:23:29 GMT

From your data,As far as i understood, Created a quarter in script.

Data:
LOAD *,
'Q' & Ceil(Month([Processed Date])/3) As Quarter
INLINE [

    Processed Date, Standard Organization Name, RTA
    3/28/2017 ,FIDELITY INVESTMENTS, $2.00
    1/12/2017 ,CHARLES SCHWAB, $3.00
    10/31/2017 ,COMPETITION - IBG, $4.00
    7/19/2017 ,MORGAN STANLEY DEAN WITTER, $5.00
    5/1/2017, VANGUARD ,$6.00
    9/18/2017 ,NEW EMPLOYER DC/DB PLAN ,$22.00
    8/9/2017 ,BANK OF NEW YORK, $23.00

];

And by creating a variable , as

='Q'&Ceil(Month(Today())/3)

Then in the Caluculated dimension in your table,

=Aggr(Only({<Quarter={"$(=vQuarter)"}>}[Processed Date]),[Processed Date])

Hope this gives your required current quarter data in the chart table.

Let me know any further info required.

PFA,

HTH,

Hirish

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 10:46:26 GMT

i am getting as null

Re: Last Quarter Data Population

HirisH_V7 — Wed, 08 Nov 2017 10:51:14 GMT

At your Dimension in your chart ,

Supress when value is null()

one Quick question, is complete column is going Null??

-Hirish

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 11:37:29 GMT

Yeah everything is showing as null

Re: Last Quarter Data Population

shraddha_g — Wed, 08 Nov 2017 11:43:53 GMT

Did you refer app attached ?

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 11:44:28 GMT

Actually i have created a measure to calculate the current year summation which is shown below.

sum({$<[PROCESSED_DT.autoCalendar.Year] = {'$(=date(max([PROCESSED_DT.autoCalendar.Year]), 'YYYY'))'},ROLLOVER_DESTINATION_TXT={'RTF','RTC'},FULL_DISTRIBUTION_IND={'Y'}>} ROLLED_TO_AMT)

do you have any suggestions to modify the same formula for quarter?

Re: Last Quarter Data Population

HirisH_V7 — Wed, 08 Nov 2017 12:09:50 GMT

Use this,

IF(Ceil(Month(Today())/3)=Ceil(Month([Processed Date])/3),

sum({$<[PROCESSED_DT.autoCalendar.Year] = {'$(=date(max([PROCESSED_DT.autoCalendar.Year]), 'YYYY'))'},ROLLOVER_DESTINATION_TXT={'RTF','RTC'},FULL_DISTRIBUTION_IND={'Y'}>} ROLLED_TO_AMT)

)

HTH,

Hirish

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 12:17:27 GMT

again null

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 12:18:36 GMT

I have referred your app....but that is not working out for me

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 12:19:45 GMT

Its showing the summation of entire RTA data

Re: Last Quarter Data Population

shraddha_g — Wed, 08 Nov 2017 12:23:12 GMT

Instead of sum() ypu can use only() there

Re: Last Quarter Data Population

Anonymous — Wed, 08 Nov 2017 12:24:51 GMT

again null