Totals by Month from Island Tables

[vishsaggi]

Hello Experts,

Need some advice/ suggestions on this please... Question inside the Doc attached.

stalwar1‌ johnw‌ SteveCat007‌ vinieme12‌

Or

Anyone who has this type of requirement.

Appreciate your time and patience.

[sunny_talwar]

This?

=Sum(aggr(if(Visit='PT' or Visit='OT' or Visit='SP', 1,0),Visit,[Charge Date], Encounter)) +

Sum(Aggr(If(LiftMonth = ChargesMonth and Location = [VIM Location], Count([Referred from VAW location])), LiftMonth, ChargesMonth, Location, [VIM Location]))

[vishsaggi]

Thanks Sunny, will look into it and get back soon.

[vishsaggi]

So sorry sunny, completely forgot to mention, that the left table has one STATE and the Right table is in different state. I mean alternate states. This is the expression i was looking for, appreciate on that, but any help when alternate state is used to get the same totals in Source1 table. Thanks very much in advance. 

[sunny_talwar]

Alternate state? Can you update the sample with what you mean?

[vishsaggi]

Yes will do that in a while. Thanks Sunny for the help.

[vishsaggi]

Its only calculation for only one location. PFA.

[sunny_talwar · Accepted solution]

This isn't perfect and very slow, but this can work for you

=Sum(Aggr(If(Visit='PT' or Visit='OT' or Visit='SP', 1,0),Visit,[Charge Date], Encounter)) +

Sum({Source2<Location>}Aggr(If(Only({Source2<Location>}LiftMonth) = Only({Source2<Location>}ChargesMonth) and Only({Source2<Location>}Location) = Only({Source2<Location>}[VIM Location]), Count({Source2<Location>}[Referred from VAW location])), LiftMonth, ChargesMonth, Location, [VIM Location]))

Lumberton doesn't come through because of name mismatch. May be it can be handled using WildMatch or SubStringCount, but I guess you get the idea

Best,

Sunny

[vinieme12]

Why don;t you create a bridge table and associate the two tables

Linking to two or more dates

[vishsaggi]

Thanks Very much Sunny, this did the trick. I will look for the processing time for this specific table and inform the user. Appreciate all your help.