Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Expressions & Variables w/ Dates

Hi all-

I've read through lots of questions and blog posts regarding how to show select/show data based on a time frame but I'm still coming up short...

I would like to show my dashboard to show the most current month (which happens to be last month, April).  I've played around with the Master Calendar, Set Expressions and Variables and can't get it to calculate right...

I'm trying to pull the Responsible $ by the Date of Incident and have it show the most current month (April 2016).

I've tried using the Set Analysis Wizard and have tried a combination of options and can't get it to calculate.  I'm wondering if the issue lies in how the Master Calendar is set-up // connected to my data?  Any helps is appreciated, getting frustrated with my limited experience in scripting...

Master Calendar

Load

TempDate AS [Date of Incident],

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

Day(TempDate) & '-' & Month(TempDate) as DayMonth,

WeekDay(TempDate) as WeekDay;

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

LOAD

min(FieldValue('Date of Incident', recno()))-1 as mindate,

max(FieldValue('Date of Incident', recno())) as maxdate

AUTOGENERATE FieldValueCount('Date of Incident');

LET vLYTD = Date(MakeDate(Year(Today())-1 , Month(Today()) , Day(Today())),'YYYY-MM-DD') ;

LET vCQTD = Date(Floor(QuarterEnd(Today())) , 'YYYY-MM-DD') ;

LET vLQTD = MakeDate( Year(QuarterEnd(Today(),-1)) , Month(QuarterEnd(Today(),-1)) -1 , day(Today())) ;

Data Table

[Rework Data]:

LOAD

2016 as [Rework Year],

    "DATE" as [Date of Incident],

    "Original Ticket Foundation" AS [Original Job #],

    "Rework Ticket Foundation" AS [Rework Job #],

    CUSTOMER AS [Client],

    "PROBLEM DESCRIPTION" AS [Description],

    "Corrective Action Status",

    "Resp Area" AS [Responsible Area],

    "% Resp",

    "Resp Area (adj)",

    Pieces,

    "% Qty",

    "Qty (adj)" AS [Responsible Qty],

    "$ REWORK",

    "% $",

    "$ Rework (adj)" AS [Responsible $ ]

FROM [lib://Rework 2016 (iwco_mlkish)]

(ooxml, embedded labels, table is [Calc Data]);

1 Solution

Accepted Solutions
sunny_talwar

How about this:

Sum({<Month = {"$(=MaxString({<Year = {$(=Max(Year))}>}Month))"}>}[Responsible $ ])

Alternatively, you can try this as well

Sum({<[Date of Incident] = {"$(='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])

View solution in original post

5 Replies
sunny_talwar

How about this:

Sum({<Month = {"$(=MaxString({<Year = {$(=Max(Year))}>}Month))"}>}[Responsible $ ])

Alternatively, you can try this as well

Sum({<[Date of Incident] = {"$(='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])

Not applicable
Author

The second solution worked! (the first one did not, it brought in many months...)

Thank you so much for your help and timely response! Can you help me understand the logic behind the second string?

Not applicable
Author

Also - how would I modify the second solution to pull in Last YTD data?

sunny_talwar

Last year YTD? or this year YTD?

For this year:

Sum({<[Date of Incident] = {"$(='>=' & YearStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident])))"}>}[Responsible $ ])

For last year YTD:

Sum({<[Date of Incident] = {"$(='>=' & YearStart(Max([Date of Incident]), -1) & '<=' & MonthEnd(AddYears(Max([Date of Incident]), -1)))"}>}[Responsible $ ])

sunny_talwar

I am just the expression to filter the dates between MonthStart for the max date and MonthEnd. To see the date range, you can add this to a text box object:

='>=' & MonthStart(Max([Date of Incident])) & '<=' & MonthEnd(Max([Date of Incident]))

and you will see that if the max date is May 15th, 2016, the above expression will show a range of May1st to May 30th. And since this range is specified in the date field, we see only Month to Date sum