is it possible to only select the last months figures in an expression. Qlikview

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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;

[sunny_talwar]

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?

[SunilChauhan]

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)

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

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'

[Anonymous]

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?

[sunny_talwar]

Yes that is absolutely correct