Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

CHART ISSUE

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

19 Replies
sunny_talwar

See if the attached sample makes sense:

Capture.PNG

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]))

Not applicable
Author

Capture.PNG

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

sunny_talwar

Close?

Capture.PNG

Not applicable
Author

Why do you have 3 Load Statements?

sunny_talwar

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

Not applicable
Author

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

Not applicable
Author

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?

sunny_talwar

Do you have MasterCalendar in your script? Can you share the code you are using for your Master Calendar?

sunny_talwar

Attached here: Re: CHART ISSUE