Avoid associations between column names in table

[nishanthi_8]

I have a two different date columns - Allocation_1 and Allocation_2. Here I have to use a set analysis expression by considering both the dates with the latest month and if selected that particular month data should be displayed. The problem here is since every row is associated it gives a wrong value for eg: Allocation_1 has May-2016 and Allocation_2 has Jun-2016. Now my expression is ,

count({<Allocation_1 =e({1<Allocation_1 ={">=$(=date(max(Allocation_1 ),'MMM-YYYY'))<=$(=date(addmonths(max(Allocation_1 ),12),'MMM-YYYY'))"}>}Allocation_1 ),Allocation_2 ={'$(=max(Allocation_1))'},[Status]-={'Open','External','Internal'}>}[Resource Id])

So if I select any month in Allocation_1 its getting associated with Allocation_2 and so its not giving proper value. How to avoid that?

[MK_QSL]

may be

count({<

     Allocation_1 =e({1<Allocation_1 ={">=$(=date(max({<Allocation_2 >}Allocation_1 ),'MMM-YYYY'))<=$(=date(addmonths(max({<Allocation_2 >}Allocation_1 ),12),'MMM-YYYY'))"}>}Allocation_1 ),

Allocation_2 ={'$(=max(Allocation_1))'},[Status]-={'Open','External','Internal'}>}[Resource Id])

[jonathandienst]

Have a look here for the way to handle multiple dates in your facts:

Canonical Date

[nishanthi_8]

any other alternative solution ?

[jonathandienst]

Several, but I think that Henric's article provides the best solution for your problem, as I understand it.

[Uday_Pasupuleti]

HI,

First create a separate master calendar without any associations(data Island). For min and max dates use 'Rangemin()' and 'Rangemax()' for those 2 date fields.

Then in your setanalysis you need to use like

Count({<Allocation_1 ={'>=$(vMinDate)<=$(vMaxDate)'},[Status]-={'Open','External','Internal'>} distinct if(Allocation_1 =Date,[Resource Id]))

Same for the second expression

Count({<Allocation_2 ={'>=$(vMinDate)<=$(vMaxDate)'},[Status]-={'Open','External','Internal'>} distinct if(Allocation_2 =Date,[Resource Id]))

If you are using this expression then you don't need to use count if. You can just use count.

Note: For every expression in the sheet you need to specify "Allocation_2 ={'>=$(vMinDate)<=$(vMaxDate)'}" or "Allocation_1 ={'>=$(vMinDate)<=$(vMaxDate)'}" .

This way if the user selects a month say Jan from the global calendar the expressions will only show the records belongs to Jan in Allocation_1 and records belongs to Jan in Allocation_2.

Thanks,

[nishanthi_8]

what is that master calendar as data island ? That means should we give the same field names for the master calendar created ?

[Uday_Pasupuleti]

Create a master calendar without any association to your table.

But to get min and max date for calendar use range sum of your data fields.

Then create 2 variables for min and max date and  as I mentioned in my previous reply use that expression in set analysis.

Thanks,

[Uday_Pasupuleti]

HI Nishanthi,

Did you sort it out?

If Yes, Could you pPlease mark the appropriate replies as CORRECT / HELPFUL so other members know that your question(s) has been answered.

Thanks,