If Statement in Chart Dimension Based on a Variable - incorrect values

[WH_Aqua]

Hello!

I wanted to ask the community for help with a certain issue. I looked for a solution on the forum, but I couldn't find it.

I have a sheet with a report that shows certain yearly KPIs ( LEADS, SALES and others) and a bar chart that shows those values per month.

I use If statement based on a variable to allow to show different KPIs on a chart. Depending on the value of the variable it should show the chosen KPI on the chart and this part works.

However, because for LEADS I need to use a different column to get the month, I also need to use IF statement in the dimension of the chart and here lies the problem.

The values I am getting are larger then they should be and if I'll just use the month dimension correct for each KPI it works as it should.

The IF statement is:
= If
(
$(vVariable) < 7
,
Month(MakeDate([NUMER_ROKU], [NUMER_OKRESU], 1)) /* for SALES */
,
Month([DATA_REJESTRACJI]) /* for LEADS */
)

So this does not work, but when I make a dimension just:

Month(MakeDate([NUMER_ROKU], [NUMER_OKRESU], 1)) /* for SALES */
or
Month([DATA_REJESTRACJI]) /* for LEADS */

Then it works correctly.

Am I missing something obvious?

I'll attach sample data and a screenshot of the report, if there is anything I should add to make things more clear, I gladly will.

Thanks in advance!

[Anil_Babu_Samineni]

Is that Variable "$(vVariable)" capturing any input or is that something calculating from different field?

[WH_Aqua]

Hello!

The variable is defined by the qsVariable extension dropdown list with fixed values from 1 - 7, where 1 is labeled "SALES", ..., 7 is labeled "LEADS". As shown on the Screen3 attached here.

[WH_Aqua]

There is also other question.

I have KPI's for SALES and LEADS on one sheet and they are based on two different date.
Let's say DateA = Date of Sale and DateB = Date of Client Registration.

How do I make it so I can use one MONTH filter to show LEADS where month(DateA = X) and SALES where month(DateB = X) but in a way that it doesn't just filter data that have either month(DateA = X) or month(DateB= X) or both those criteria, but that it uses the month(DateA = X) data only for the SALES KPI and month(DateB = X) data only for LEADS KPI?

Is that possible at all? Should I maybe create separate post about this?

I attached the way the filters and KPIs are presented. So in that form I would like to have different data filter for LEADS KPI and other filter for all other filters. Is that doable?

Thanks for responding 🙂

[lorenzoconforti]

Regarding the if statement not working, there must be something else going on (maybe you've misspelled something) because it's working fine for me (see attached)

Regarding the date, you need to used what is called a canonical date (https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578); see dashboard attached

[WH_Aqua]

Thanks for the answer.

Maybe I should State it clearly in the beginning, sorry for that.

For SALES what you do is take a SUM of OBROT column, that's true.

However, for LEADS the measure is not to sum anything, but to count distinct client numbers (NUMER_KONTRAHENTA) for selected year.

But you may be right, that the formula is fine and something else is wrong, but for the life of me, I can't find it.

Thanks for the info about canonical date, I'll look into it.

[lorenzoconforti]

The measure can be a count no problem (see attached)

Can you share your dashboard? If not, can you create a subset of it that you can share?

[WH_Aqua]

I've looked into your dashboard and there is something not right as well, the filtering doesn't work as I think it should, If I use the filter for a month, it should give scores for both measures for only those months. But look at the screen attached, it does not do that.

Or is it my mistake?

Unfortunately, I cannot share the dashboard I am using as of now.

[lorenzoconforti]

I think I got the two mixed in the variable (see attached). Regardless, bear in mind that when you have a calculated dimension and you make a selection, you are effectively selecting the fields that have been used in that dimension. When you then change from SALES to LEADS you are not filtering by Mar for the new calculated dimension that you are displaying but for the old one you had when you made your selection

[WH_Aqua · Accepted solution]

Sorry for the late reply Lorenzo.

I wanted to let you know that you were right about the Canonical Date, it was the solution for filtering with 2 dates, great stuff, thanks for showing me the right direction.

The trouble with the If Statement on Chart persisted even though the data and the formula should be correct.

I've found a workaround though.

I created 3 charts, each showing a part of the If statement and I used ShowHide Container to show each one depending on the variable value.

It works fine now 😄