Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
nboccassini
Partner - Creator
Partner - Creator

DATA MODEL TO USE OTHER PERIOD IN DIMENSION

Hi all,

I have an application that calculate more kpi (approx 400) on different period.

I have a fact table (approx 3.000.000 rows) like this:

CODELOCATIONPERIODVALUE
code1italyY2020100
code1franceY2020200

 

Period can be a month (ex 012020) a quarter (ex Q12020), an half (H12020) or a Year (Y2020)

The prolem is that some KPI use more period in expression. Using a pivot, I actually don't use PERIOD in dimension and I'm using variable per PERIOD value. For example (vy1='Y2020'):

 

SUM({<CODE={'1231'}, PERIOD={"$(='1H'&(Right(vy1,4)-2))"} >}VALUE)+
SUM({<CODE={'2456'}, PERIOD={"$(vy1)"} >}VALUE)

 

This solution has a performance problem because 400 KPI calculated by expression like this need more memory (out of memory error).

So I need to use PERIOD in dimension in pivot but I don't know how.

The idea is use a expression like this:

 

SUM({<CODE={'1231'}, PERIOD_P={"1H2"} >}VALUE)+
SUM({<CODE={'2456'} >}VALUE)

 

where PERIOD_P=1H2 means first half (1H) two year ago (2)=1H2

How restructure model to use this approach?

1 Reply
marcus_sommer

Just from your description it's hard to guess what do you want to do. Ideally you have just a simple expression like sum(Value) and everything else happens on appropriate created dimension-values and/or selections from the users.

In regard to your mentioned overlapping period-values you may bundle them with The-As-Of-Table and/or some kind of flagging them - avoiding in each case variables/expressions in the period-condition. Means to end with expressions like:

sum(Value)
sum({< Flag = {1}>} Value)
sum(Value) * Flag
sum({< Flag = {1}, Period = p(Period) >} Value) 

or similar.

- Marcus