Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is the sample data, I am trying to create a chart with the following Calculated dimension and I am using the value list function to create the dimension
. My valueList is ('Incurred ','Paid')
. Incurred = Sum(Ind Paid-Ind Rec) and Paid =(Ind Paid+ Exp Paid)
I am using Two Diemsions in the Bar chart
1st one is
$(vValuelist)and second is 'Q'& Ceil(Month/3)
I wanna calculate the Incurred and Paid for each Quarter represented by Bars, for example If I select <= 'Mar' I should see the Bar for Q1 for Incurred and Paid seperatley
If Month > Mar and <= Jun' then I expect to see Bar 'Q1' and Bar 'Q2' again for both Incurred and Paidand so on. and Q2 includes Q1 and Q2, Q3 includes (Q1,Q2 and Q3), its all cumulative.
Please let me know if this good enough info.
Attached is the real Sample data
See if the attached sample makes sense:
Script:
Table:
LOAD *,
MakeDate(Year, Month, 1) as Date,
MonthName(MakeDate(Year, Month, 1)) as MonthYear;
LOAD Year,
Month(Date#(Month, 'MMM')) as Month,
[IND PAID],
[EXP PAID],
[IND REC],
[EXP REC];
LOAD Year,
Month,
[IND PAID],
[EXP PAID],
[IND REC],
[EXP REC]
FROM
Sample_Data_Quarters.xls
(biff, embedded labels, table is Sheet1$);
Dimensions
1) ='Q' & Ceil(Month/3)
2) =ValueList('Incurred', 'Paid')
Expression
=If(ValueList('Incurred', 'Paid') = 'Incurred', Sum({<Month = {"$(='>=' & 1 & '<=' & Max(Month))"}>}[IND PAID] - [IND REC]), Sum({<Month = {"$(='>=' & 1 & '<=' & Max(Month))"}>}[IND PAID] + [EXP PAID]))
I want the chart to look like this in a Trellis Chart option. The circles are expected values. Which I can add once I figure out the bars
Close?
Why do you have 3 Load Statements?
Just to make sure that Time data (Month and date) are read as dual field else the set analysis won't work properly. I am guessing those steps would not be required for your actual data if you have Month field created from your date field like this -> Month(DateField) as Month
Yes it is already created , so I dont need any changes to the script the, but I tried the expression , not the same result as yours
Th is exactly what I am looking for . How did you do it? do u have a QVW for that and just to make sure, I dont need to make any change to the script?
Do you have MasterCalendar in your script? Can you share the code you are using for your Master Calendar?
Attached here: Re: CHART ISSUE