Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Data modeling

Hello

I am trying to create a data model and Qliksense architecture for an application, the requirement has several KPIs (some of which are yet to be defined) , in some indicators I have the famous 'count(distinct IDs)' and the dimensions are multiple , some are related some not.The data volume is huge (necessary input tables vary from  1000 rows to 200 millions rows) and the data keeps on growing by the triple each year. so the first instinct I got was to aggregate (gather all dimensions keys and Facts into one big fact table, aggregate and then have like 400 000 rows fact table with small dimension tables around) , tried to aggergate the KPIS with 'count(distinct IDs)' but the result is different from the actual correct result

example: count(distinct IDs) OF year=2015

  • non aggregated: 100 000

  • aggregated:  127 000

I have attached a cross table  of indicators and dimensions to show which indicator is calculated at wich level, (the Dimension Dim1... Dim4 are a hierarchy Dime4 being the Macro level, the others  O, A, X.. are not related)

Any idea Would be very helpful

Regards

2 Replies
Not applicable

Re: Data modeling

what is your aggregated data granularity?

While aggregating data , create new field with Count( Distinct ID) as Counter

Use Sum(Counter) as your final expression.

Not applicable

Re: Data modeling

That's the thing about sum(flag) gives the wrong values as I mentionned before so the only thing I can do is to load Data as it is and then do the county(distinct) directly