Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
akuttler
Creator
Creator

AS OF DATE Dimension

Hello, I am trying to create a pivot table with the total credit balances AS OF the end of each month. 

But my 'EndOfMonth' Date dimension is giving me null.

The Dimension 'EndOfMonth' defined as :

=IF(Rangemax(TDate) <= '01-31-2018', 'January 2018',
IF(RangeMax(TDate) <= '02-28-2018', 'February 2018',
IF(Rangemax(TDate) <= '03-31-2018', 'March 2018',
IF(Rangemax(TDate) <= '04-30-2018', 'April 2018',
IF(Rangemax(TDate) <= '05-31-2018', 'May 2018',
IF(Rangemax(TDate) <= '06-30-2018', 'June',
IF(Rangemax(TDate) <= '07-31-2018', 'July 2018',
IF(Rangemax(TDate) <= '08-31-2018', 'August 2018',
IF(Rangemax(TDate) <= '09-30-2018', 'September 2018',
IF(Rangemax(TDate) <= '10-31-2018', 'October 2018',
IF(Rangemax(TDate) <= '11-30-2018', 'November 2018',
IF(Rangemax(TDate) <= '12-31-2018', 'December 2018'))))))))))))

 

My Data Load script :

LOAD
DATE(TDate,'MM-DD-YYYY') as TDate,
SQL SELECT
CAST(FLOOR(CAST([TransactionDate] as float)) as DATETIME) as TDate

FROM dbo.patientaccountreport

I don't know if I am way off or what I'm missing. 

Thanks in advance.

Labels (2)
2 Replies
Anil_Babu_Samineni

Try this?

=IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('01-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'January 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('02-28-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'February 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('03-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'March 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('04-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'April 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('05-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'May 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('06-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'June',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('07-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'July 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('08-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'August 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('09-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'September 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('10-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'October 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('11-30-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'November 2018',
IF(Date(Rangemax(TDate),'MM-DD-YYYY') <= Date(Date#('12-31-2018', 'MM-DD-YYYY'), 'MM-DD-YYYY'), 'December 2018'))))))))))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
PradeepReddy
Specialist II
Specialist II

try to avoid calculated dimension in charts, if possible create the same in script.
It improves the performance.

Try something like this, in script

Input:
LOAD PatientID, Balance
DATE(TDate,'MM-DD-YYYY') as TDate,
Date(monthend(DATE#(Date,'MM-DD-YYYY')),'MMM YYYY') as TMonthEnd;
SQL SELECT
PatientID, Balance,
CAST(FLOOR(CAST([TransactionDate] as float)) as DATETIME) as TDate;

Left Join(Input)
LOAD
PatientID, TMonthEnd,
Date(max(TDate),'MM-DD-YYYY') as TMaxDate
Resident Input
Group by PatientID,TMonthEnd;

Process_Final:
Load *,
IF(TDate=TMaxDate,'Y','N') as MonthEnd_Flag
Resident Input;
Drop Table Input;

Now Create bar chart (As per your requirement)
with 2 dimensions.. TMonthEnd, Patiend
expression... sum({<MonthEnd_Flag={'Y'}>}Balance)