topic Re: Conditional expression based on dimension in QlikView

Conditional expression based on dimension

Konstantin_Novikov — Sat, 16 Nov 2024 21:12:24 GMT

Dear all,

There is a chart showing monthly price dynamics for two years (current & previous).
There are also two straight lines showing avg price of current year and previous year.

On the same sheet user can set filters by month and year, but these filters do not affect the chart. It always shows full max year from the selection + the previous one (i.e. 2019 + 2018, 2018 + 2017, etc).
All other selections should be applied to the chart. I.e., if user needs data of specific product or customer.

Is it possible to show Avg CY line only for this year, and Avg PY line only for prev.year? (see example below)

I tried If function "if(Year = max(Year), then)", but it makes Year & month selections applied:
If March is selected, it shows single point for March;
If only 2019 year is chosen, there is nothing shown for 2018.

Any advice on the topic is appreciated.
Thank you!

Re: Conditional expression based on dimension

anushree1 — Thu, 21 Mar 2019 14:05:10 GMT

use total keyword in your avg expression

like avg(total expressiondefinition)

Re: Conditional expression based on dimension

olivierrobin — Fri, 22 Mar 2019 15:26:43 GMT

did you try

year=max({1}year) ?

Re: Conditional expression based on dimension

Konstantin_Novikov — Mon, 25 Mar 2019 12:54:03 GMT

Thank you for your answers. I got some progress using max({1}Year). Now the graph is perfect if 2019 year is chosen and there is no selection by month.

There are two problems now:
1) If 2018 is chosen, PY line disappears;
2) If there is a selection by month, PY line is shown for both years, and CY line is shown only for selected months

Current expressions are the following:

Avg CY:

If(Calendar.Year = max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year))'} > }Volume)
)

Avg PY:

If(Calendar.Year <> max({1}Calendar.Year),
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Sales)
/
sum(TOTAL { < Calendar.MonthName = , Calendar.Year = {'$(=max(Calendar.Year)-1)'} > }Volume)
)

Re: Conditional expression based on dimension

olivierrobin — Mon, 25 Mar 2019 13:42:41 GMT

hello,

in your set analysis, try to add

Calendar.Month=

which will inhibit all selections made on month

Re: Conditional expression based on dimension

Konstantin_Novikov — Tue, 26 Mar 2019 11:17:08 GMT

There are two fields: Calendar.YearMonthName and Calendar.MonthName.
The first one is dimension on X axis, and the second one is used by users for period selection.
In my expressions there are already "Calendar.MonthName = " identifiers. I also tried adding "Calendar.YearMonthName = ", but both options (and combination) do not work.