Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
slribeiro
Partner
Partner

set analysis ignoring

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,

YearMonthCount of NoHeader 4
2016Jan1650
2016Feb0 or null1050
1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

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
Partner
Partner
Author

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


Capture.PNG

petter
Partner
Partner

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

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
Partner
Partner
Author

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
Partner
Partner
Author

Jan 2016

sunny_talwar

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.

View solution in original post