Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
Try,
sum({<QuarterStart = {"$(=date(addmonths(max(QuarterStart),-3)))"},ROLLOVER_DESTINATION_TXT={'RTF','RTC'},FULL_DISTRIBUTION_IND={'Y'}>}ROLLED_TO_AMT)
what is your data?
what is the expected output according to your data..
Please elaborate
try this in your script Date/calendar table.
'Q' & Ceil(Month([DateField])/3) As Quarter,
Regard
Vikas
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 |
do i have to run this script in the data loader?
Create QuarterStart in your Calendar Script as QuarterStart(DateField) as QuarterStart.
In Chart,
Use sum({<QuarterStart = {"$(=max(QuarterStart))"}>}RTA)
I have created a quarterstart variable but that is not working...let me try create a script
PFA
Hi
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
i am getting as null