Re: Need help with calculations - Qlik Community

Anonymous · ‎2017-02-26

Hi All,

I am new to qlikview. Trying to create a straight table that shows the KPI value for the selected month and trend for the last 12 months.

However, I am having the below issues:

• Measure Names should be filtered for the selected ‘Measure Domain’. Currently the table is showing all the measure names, not filtering for the measure domain selection.

•KPI value is also not changing based on AreaID selection. It is showing the same value for all the AreaID selected.

KPI value = if (DataType = 'Percent', Num( avg( KPI_Value), '#,#0.0%'),

if (DataType = 'Number', Num( avg( KPI_Value), '#,##0')))

• The KPI trend shows the last 12 month trend. I am using the below expression. I want to display to display whatever the existing values for the 12 months without averaging. However, the set analysis expression is asking for a aggregation (sum/avg etc.)

•

=avg({1 <PeriodKey_End = {"$(='>' & Date(AddYears(Max(PeriodKey_End), -1), 'YYYY-MM-DD') & '<=' & Date(Max(PeriodKey_End), 'YYYY-MM-DD'))"}>} KPI_Value)

I ma attaching the sample qvw file. Please can you help.

Thank you.

ahaahaaha · ‎2017-02-26

Hello again,

Excuse me, but I do not like your data structure. Poor, when the synthetic key connects more than one field of the two tables. In your example (according to the attached file) into a key part of 8 fields. I think it is necessary to start with the creation of the right related to the structure of tables.

Regards,

Andrey

sunny_talwar · ‎2017-02-26

May be try this if MeasureDomain is the only selection you want to allow for your this dimension:

=Avg({1<PeriodKey_End = {"$(='>' & Date(AddYears(Max(PeriodKey_End), -1), 'YYYY-MM-DD') & '<=' & Date(Max(PeriodKey_End), 'YYYY-MM-DD'))"}, MeasureDomain = p(MeasureDomain)>} KPI_Value)

or this

=Avg({$<PeriodKey_End = {"$(='>' & Date(AddYears(Max(PeriodKey_End), -1), 'YYYY-MM-DD') & '<=' & Date(Max(PeriodKey_End), 'YYYY-MM-DD'))"}>} KPI_Value)

Basically when you use 1, you are telling your TREND expression to ignore selections in all the fields. If you want to allow selection in one field (Measure Domain), then you specify it using p() function. If you want to allow for all selections, you need to remove 1 and use $ instead.