Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nishanthi_8
Contributor

Avoid associations between column names in table

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?

8 Replies

Re: Avoid associations between column names in table

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])

MVP
MVP

Re: Avoid associations between column names in table

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

Canonical Date

nishanthi_8
Contributor

Re: Avoid associations between column names in table

any other alternative solution ?

MVP
MVP

Re: Avoid associations between column names in table

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

uday_pasupuleti
Contributor II

Re: Avoid associations between column names in table

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
Contributor

Re: Avoid associations between column names in table

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

uday_pasupuleti
Contributor II

Re: Avoid associations between column names in table

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
Contributor II

Re: Avoid associations between column names in table

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,

Community Browser