use Exclude to filter out

Gabbar — Mon, 12 Sep 2022 08:13:19 GMT

In this chart i am counting distinct ref_id, But i want that if a reference Id has been counted then it should not be counted again in any upcoming year. The equation of this chart is

Count({<
DateType={'REf_Id Created On'}
>}distinct REf_Id )

I tried these equation to sort out
Count({<
DateType={'REf_Id Created On'},REf_Id =e({<DateType={'REf_Id Created On'},DateKey={"<=num(addyears(Max(DateKey),-1))"}>})
>}distinct REf_Id )

and this

Count({<
DateType={'REf_Id Created On'},REf_Id =e({<DateType={'REf_Id Created On'},DateKey={"<=num(addyears(Max(DateKey),-1))"}>}REf_Id )
>}distinct REf_Id )

The graph remained same.

Please Guide.

Re: use Exclude to filter out

henrikalmen — Wed, 21 Sep 2022 11:03:23 GMT

I believe you need to solve this in loadscript, because set expressions only evaluate once per table/chart - not per dimension. I would probably try to create an additional facts table in the data model, where ref_id is only used for the first date occasion it exists on.

topic use Exclude to filter out in App Development

use Exclude to filter out

Re: use Exclude to filter out