topic Re: set analysis ignoring in QlikView

set analysis ignoring

slribeiro — Thu, 05 Jan 2017 16:58:34 GMT

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

Re: set analysis ignoring

vishsaggi — Thu, 05 Jan 2017 17:14:32 GMT

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)

Re: set analysis ignoring

slribeiro — Thu, 05 Jan 2017 17:21:32 GMT

No, it's not working properly.

Re: set analysis ignoring

sunny_talwar — Thu, 05 Jan 2017 17:30:19 GMT

May be this:

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

Re: set analysis ignoring

petter — Thu, 05 Jan 2017 17:31:57 GMT

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?

Re: set analysis ignoring

petter — Thu, 05 Jan 2017 17:49:05 GMT

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....

Re: set analysis ignoring

slribeiro — Thu, 05 Jan 2017 18:30:19 GMT

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

Re: set analysis ignoring

sunny_talwar — Thu, 05 Jan 2017 18:40:27 GMT

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

Re: set analysis ignoring

slribeiro — Thu, 05 Jan 2017 18:49:28 GMT

Jan 2016

Re: set analysis ignoring

sunny_talwar — Thu, 05 Jan 2017 22:09:09 GMT

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.