topic Re: Need help set analysis in App Development

Need help set analysis

tgumbing — Thu, 06 Jul 2023 13:06:04 GMT

Hello,

I have a Table where I have to calculate per month a expression based on different date.

The dimension:
=AQMP.MONTH

The expression:
count(DISTINCT {<AQMP.YEAR={$(=Max(AQMP.YEAR))}, AQMP.MONTH={"<=$(=Max(AQMP.MONTH))"}>} [AQMP.N° AQMP])
/
count(DISTINCT {<AQMP.YEAR=, AQMP.MONTH=, TSF.YEAR={$(=Max(AQMP.YEAR))}, TSF.MONTH={"$(=only(AQMP.MOIS))"}>} TSF.Product)

In the second calculation I need to calculate for each month from the dimension the TSF.Product. And the Table TSF doesn't have the same calendar. And it must be aggregate per month from dimension AQMP.MONTH

Re: Need help set analysis

Chirantha — Thu, 06 Jul 2023 13:56:08 GMT

Hey there,

If I understand correctly, you want to calculate the count of distinct TSF.Product for each month from the dimension AQMP.MONTH, even though the table TSF doesn’t have the same calendar. To do this, you could try using an advanced search in your set analysis to match the values of TSF.MONTH with the corresponding values of AQMP.MONTH. For example:

count(DISTINCT {<AQMP.YEAR={$(=Max(AQMP.YEAR))}, AQMP.MONTH={"<=$(=Max(AQMP.MONTH))"}>} [AQMP.N° AQMP])
/
count(DISTINCT {<AQMP.YEAR=, AQMP.MONTH=, TSF.YEAR={$(=Max(AQMP.YEAR))}, TSF.MONTH={"=sum({<AQMP.MOIS={'$(=only(AQMP.MOIS))'}>} 1)"}>} TSF.Product)