Problem with Set Analysis that Does Not Ignore a Dimension

[Eli181]

Hi.

I have the following problem that I cannot solve despite hundreds of Set Analysis expressions:

Since in my visualization table the yearmonth variable is derived from the manufacturing date, the count of invoices is limited to the number of batches manufactured that month, and not the entire 17 billed that month. I tried

billed_batches = count({$<Manufacturing_Date = >} batchID)

or

billed_batches = count({$<yearmonth = >} batchID)

or

billed_batches = count({$-1<yearmonth={"20*"}>}batchID)

... but no good results 😞

Or should I use the Aggr function:

1st: calculate 

sum(Aggr(count((batchID)),yearmonthbilling))

2nd: calculate

the allocation of this previous out put across yearmonth ... but have no clues about the syntax.

Could anyone please help?

Thank you in advance.

Eli

[marcus_sommer · Accepted solution]

You may try it with something like this:

count({< Batch = {"=month({Manufacturing Date])=month([Billing Date])"}>} Batch)

but often it's needed to create a canonical calendar to link all related date-fields within an extra calendar. More background could you find here: Canonical Date

- Marcus

[Eli181]

Thank you so much for helping me fixing this issue.

The Canonical Calendar was the solution, and henric_cronstrom's post was of great help

Awesome !!!