Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
is it possible to pick up ONLY the last months figures in an expression? I.e. to show in a charts results :-
Data from table example. Therefore I would only want to show March
Date Media State
01-02-2016 TV Vic
03-03-2016 Print NSW
Cheers
LC
Hi,
try
Count({<%MediaDate={">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Outlet)
careful with date format and if you need month end of today then use MonthEnd(Today()).
Kind Regards,
You sure can, what is your expression right now? You can try like this in your set analysis:
{<Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'MM-DD-YYYY') & '<=' & Date(MonthEnd(Max(End)) , 'MM-DD-YYYY')"}>}
I was not sure if your date format is MM-DD-YYYY or DD-MM-YYYY. If it is not MM-DD-YYYY, then change it accordingly above.
If it still doesn't work, please provide a sample to help you better
HI Sunny,
I’m a little lost with this. I need to pick up only the “current months data”. So for example today the Chart will only display data from May. The following is the calendar I have created for the media chart. Below the dotted line you can see the raw data that I pull through to use in the chart. The screen dump is the actual table pulled through into qlik.
Do I still use this formula you emailed me to could the Segments for the current month (i.e. replacing Date with ‘%MediaDate ‘)?
ASAMediaCalendar:
Load
TempDate AS %MediaDate,
Week(TempDate) As MediaDateWeek,
Year(TempDate) As MediaDateEmpYear,
Month(TempDate) As MediaDateEmpMonth,
Day(TempDate) As MediaDateEmpDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as MediaDateEmpQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as MediaDateEmpWeekYear,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as MediaDateEmpYearMonth,
WeekDay(TempDate) as MediaDateEmpWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Yes, but I don't know what is the expression you are using. Lets assume that this is your expression
Count(Measure)
Replace it with this:
Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Max(%MediaDate)), 'MM-DD-YYYY') & '<=' & Date(MonthEnd(Max(%MediaDate)) , 'MM-DD-YYYY')"}>}Measure)
Now if this isn't dynamic based on the selections made in %MediaDate field and should always show today's month data, then replace Max(%MediaDate) with Today() like below
Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Today()), 'MM-DD-YYYY') & '<=' & Date(MonthEnd(Today()) , 'MM-DD-YYYY')"}>}Measure)
Does it make sense?
In script you could use
Date(MonthName(%MediaDate,'MMM-YY') as MediaMonth
Count({<MediaMonth= {$(=Date(Max(MediaMonth),'MMM-YY'))}>}Measure)
Count({<MediaMonth= {$(=Date(Max(MediaMonth),'MMM-YY'))}>}Measure)
Hi Sunny,
Still struggling… tried everything. Grhhhhhh ☺.
Here is the table and the Media date is format “DD-MM-YYYY”. So that’s correct.
There is definitely data there as I have checked the loaded data table:-
The Dimensions are:-
The one Expression is:-
Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Today()), 'MM-DD-YYYY') & '<=' & Date(MonthEnd(Today()) , 'MM-DD-YYYY')"}>}Outlet)
But the chart comes up with “No data to display”??
I’ve tried using the calendar yearMonth, using today(). Could it be the MediaDate in the upload from excel needs to be reformatted (not sure how to do this as
convert (varchar,MediaDate,103) only works in excel).
ASAMedia:
LOAD Outlet,
Segment,
Target,
Date as %MediaDate,
Distribution,
State
;
Directory;
LOAD Outlet,
as Segment,
as Target,
Date,
Distribution,
State,
Month
FROM
(ooxml, embedded labels, table is );
by using the set analysis to give the current month or max month
for example
sum({<Year={$(max(Year)),Month={$(max(Month))}>}sales)
may be it is useful
You need to check the format once again
Is it DD/MM/YYYY as the supplied data or is it like MM-DD-YYYY as mentioned in the expression? These mismatches has the potention to show 'no data to display'
Hi Sunny,
the %MediaDate format is 'DD/MM/YYYY'. I implemented that change but its still not working. Can you help me to break the following down so i can understand the formula please?
so far i have :-
Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Today()), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Today()) , 'MM/DD/YYYY')"}>}Outlet)
My understanding of the first part <%MediaDate= {"$(='>=' & Date(MonthStart(Today()),'MM/DD/YYYY').
is the underlined section creating a date, that is the start of the month according to todays date? and using format = 'MM/DD/YYYY so its the same format as %MediaDate. Is my understanding correct?
Yes that is absolutely correct