in

Prologika Forums

Business Intelligence to the Masses

FILTERING OUT MEASURES WITH ZERO VALUE

  • 07-25-2006 10:01 PM In reply to

    Re: FILTERING OUT MEASURES WITH ZERO VALUE

    I can't test your query but try the following by switching to Query Mode:

    SELECT NON EMPTY {[Measures].[Billed Sales Amount]} ON COLUMNS,

    NON EMPTY {[CUSTOMER JOB].[By Cust-Title-Job].[Title Name].ALLMEMBERS} 

    HAVING Axis(0).Item(0) > 5000 ON ROWS

    FROM [DW PREP ARCHIVE]

    WHERE

    ( (lastperiods(12,tail(filter({[Job Complete Date].[Calendar Full].[Month].members* [Measures].[Billed Sales Amount]} as s, Not IsEmpty(s.current)),1).item(0).item(0))), [PREP CONTROL HDR].[Bill Formats].&[19] )

    Having filters after NON EMPTY is applied. It can take a tuple too, e.g.

    HAVING([Measures].[Billed Sales Amount], [Date].[Year].[2006] > 5000) to flter titles where Billed Sales Amount > 5000 for year 2006.

View Complete Thread
Copyright © 2005 Prologika, LLC
Powered by Community Server (Commercial Edition), by Telligent Systems