set analysis ignoring

[slribeiro]

Hello all.

I hope you can help me on this, because I'm kind of stucked.

Imagine I have 3 tables. 1 fact table, 1 master calendar and 1 dimensions table

I attached an example...

Now what I want as final table is to count the number of "No" ignoring the current dimensions and relate to the created dates.

So, instead of the table in the qvw file, if I select the "Year" 2016 I want to see,

Year	Month	Count of No	Header 4
2016	Jan	1	650
2016	Feb	0 or null	1050

[vishsaggi]

May be this your looking for? Try and let me know?

in your expression Count(No) use this

= = Aggr(Count({< Year=, Month=>}No), CreatedYear, CreatedMonth)

[slribeiro]

No, it's not working properly.

[sunny_talwar]

May be this:

Count(Aggr(If(MakeDate(CreatedYear, Month(Date#(CreatedMonth, 'MMM'))) = MakeDate(Year, Month(Date#(Month, 'MMM'))), No), Year, Month, CreatedMonth, CreatedYear))

[petter]

What is No in the Dimension table? Are they some kind of budgets or targets to be achieved?

There is clearly something with the datamodel or the actual populated in the tables that might not be right.

What are you trying to count?

[petter]

This should definitely be solved at the data level. The associations in the test data are wrong or corrupt. Fixing them in your expressions will create lots of trouble when you create your visualizations.

#1: A Calendar dimension should be as complete as possible going from the oldest date to the newest without holes.

      So it should go from Jan 2013 up to at least Jan 2016.

#2: The associations that are in the Fact table are just not correct.

               Example from the first row of the Fact Table:

                              KeyDim, KeyCalendar, Value

                                 1,1,100    

                              Here: KeyDim=1 gives Jan 2013 and KeyCalender=1 gives Dec 2015

                               It should be

                                1,4, 100

Maybe I have misunderstood so you can convince me that your data is correct....

[slribeiro]

it's close, but the thing is that I want every item created in that yearmonth.

What happens if there are no lines in the fact table and in the Dimension table we have the following line.

KeyDim, No, CreatedMonth, CreatedYear

5,400,CreatedMonth,CreatedYear

I want that Jan have the value 2 in the count even if one of the items doesn't have any value

[sunny_talwar]

In your example above what are the values for CreatedMonth and CreatedYear

[slribeiro]

Jan 2016

[sunny_talwar · Accepted solution]

I think petter-s‌ is right, its time for you to rethink you data model here. Seems like you have two different kind of dates in your application and Canonical Date may be the best way to handle this.