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]

Can you try this:

Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Today()), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Today()) , 'DD/MM/YYYY')"}>}Outlet)

You mentioned DD/MM/YYYY and used MM/DD/YYYY. The format is important, please make sure to get that right

[Anonymous]

yeh.  already tried that.  no luck

[sunny_talwar]

Would you be able to share how %MediaDate is getting created in the script (the load statement where it might be getting manipulated or where it is brought in.

And also share your environmental variables on the main tab. Specifically interested in seeing the SET DateFormat =

Finally, can you make sure that %MediaDate is a datefield only and there is no time component attached? You can do this by creating a straight table with %MediaDate as dimension and Num(%MediaDate) as the expression. If you see decimal numbers than it is a timestamp rather than just a date.

[Anonymous]

looks like its definitely a date then:-

.....................................

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';

..........................................

its being picked up from an excel spreadsheet (same data attached) and the script to bring it in is:-

LOAD Outlet,

     Segment,

     Target,

     Date as %MediaDate,

     Distribution,

     State;

Directory;

LOAD Outlet,

     [Name of segment] as Segment,

     [Target market] as Target,

     Date,

     Distribution,

     State,

     Month

FROM

[RawData\Reporting - Data Spreadsheet.xlsx]

(ooxml, embedded labels, table is [(ASA) Media Mentions]);

[Anonymous]

i've tried to load the MonthStart and MonthEnd into the calendar as well:-

ASAMediaCalendar: 

Load 

  TempDate AS %MediaDate,

  MonthStart(TempDate) as MediaMonthStart,

  MonthEnd(TempDate) as MediaMonthEnd,

[sunny_talwar]

Here try this:

LOAD Outlet,

     Segment,

     Target,

     Date(Floor(Date)) as %MediaDate,

     Distribution,

     State;

Directory;

LOAD Outlet,

     [Name of segment] as Segment,

     [Target market] as Target,

     Date,

     Distribution,

     State,

     Month

FROM

[RawData\Reporting - Data Spreadsheet.xlsx]

(ooxml, embedded labels, table is [(ASA) Media Mentions]);

Count({<%MediaDate= {"$(='>=' & Date(MonthStart(Today())) & '<=' & Date(MonthEnd(Today()))"}>}Outlet)

[PrashantSangle · Accepted solution]

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,

[Anonymous]

Thank-you so much for all your help Sunny.

[sunny_talwar]

So did everything workout in the end? Are you able to resolve your issue?

[Anonymous]

Yes thanks. I think it was the formats that I wasn’t paying enough attention to.

This one worked in the end:-

Count({<%MediaDate={">=$(=Date(MonthStart(Today())))<=$(=Date(MonthEnd(Today())))"}>}Outlet)

Sunny, if i want to only show last month’s figures I would???

I can get the end of last month by using =Date(MonthStart-1 but how can I get the start of the last month?