Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Shortlist only continuous data points in a column

Hello All, I want to create a column which returns the same values of data if they are contnious in nature.   For Example:- If there are 9 or more continious datapoints in sequence then, i should ...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous 

     

    To do the shortlisting for each key, we can do like this.

    I added another key to test. Please see file attached and see the calculated column

     

    Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] < EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] > EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], ASC
        ),
        [date]
    )
VAR Date1 =
    IF ( ISBLANK ( BLANKROWDatebefore ), DATE ( 1900, 1, 1 ), BLANKROWDatebefore )
VAR Date2 =
    IF ( ISBLANK ( BLANKROWDateafter ), DATE ( 3000, 1, 1 ), BLANKROWDateafter )
RETURN
    IF (
        [value] <> 0
            && COUNTROWS (
                FILTER ( Table1, [key] = EARLIER ( [key] ) && [date] < Date2 && [date] > Date1 )
            ) > 9,
        [value]
    )

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Zubair_Muhammad ,

 

I am sorry. I cannot share the data file. I have replaced the blanks in data by zeros. Can you guide me with the further step or the show the change in formula?

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Anonymous 

 

Here is the revision

File attached as well

 

Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [date] < EARLIER ( [date] ) && [value] = 0 ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER ( Table1, [date] > EARLIER ( [date] ) && [value] = 0 ),
            [Date], ASC
        ),
        [date]
    )
VAR Date1 =
    IF ( ISBLANK ( BLANKROWDatebefore ), DATE ( 1900, 1, 1 ), BLANKROWDatebefore )
VAR Date2 =
    IF ( ISBLANK ( BLANKROWDateafter ), DATE ( 3000, 1, 1 ), BLANKROWDateafter )
RETURN
    IF (
        [value] <> 0
            && COUNTROWS ( FILTER ( Table1, [date] < Date2 && [date] > Date1 ) ) > 9,
        [value]
    )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    To do the shortlisting for each key, we can do like this.

    I added another key to test. Please see file attached and see the calculated column

     

    Column =
VAR BLANKROWDatebefore =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] < EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], DESC
        ),
        [date]
    )
VAR BLANKROWDateafter =
    MINX (
        TOPN (
            1,
            FILTER (
                Table1,
                [key] = EARLIER ( [key] )
                    && [date] > EARLIER ( [date] )
                    && [value] = 0
            ),
            [Date], ASC
        ),
        [date]
    )
VAR Date1 =
    IF ( ISBLANK ( BLANKROWDatebefore ), DATE ( 1900, 1, 1 ), BLANKROWDatebefore )
VAR Date2 =
    IF ( ISBLANK ( BLANKROWDateafter ), DATE ( 3000, 1, 1 ), BLANKROWDateafter )
RETURN
    IF (
        [value] <> 0
            && COUNTROWS (
                FILTER ( Table1, [key] = EARLIER ( [key] ) && [date] < Date2 && [date] > Date1 )
            ) > 9,
        [value]
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Zubair_Muhammad ,

     

    I have tried the above method but still I am not getting the expected result. 

    Can we include the key in the formula as well? 

    Becasue my dataset can be uniqly identified on the basis of key.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Zubair_Muhammad ,

     

    Superb!! This query works excellently for my dataset..

    Thanks for your contnious help :-)