topic Re: Count Distinct IF (Conditions based on variables) in QlikView

Count Distinct IF (Conditions based on variables)

kaisdeco — Sat, 16 Nov 2024 18:14:22 GMT

I know you've already posted similar cases and gotten answers. Based on them I have been able to solve some issues that I had but still present one.

I have to count some codes of the current year and in the corresponding month when the difference between two dates is 0.

This is the code that has issues:

COUNT(DISTINCT(IF($(DIFF_YEAR) = 0 AND [YEAR] = {'CYTD'} AND MONTH = $(=MONTH_NO), CODE)))

When I use the following instruction it works:
COUNT(DISTINCT(IF($(DIFF_YEAR) = 0, CODE)))

But when I add AND [YEAR] = {'CYTD'} and MONTH = $(=MONTH_NO). Does not show results.

Thank you for your help.

Re: Count Distinct IF (Conditions based on variables)

jwjackso — Tue, 11 Aug 2020 21:22:47 GMT

If CYTD and MONTH_NO are variables,

COUNT(DISTINCT(IF($(DIFF_YEAR) = 0 and [YEAR] = $(CYTD) and MONTH=$(MONTH_NO), CODE)))

Also, if you put single quotes around $(CYTD) in the IF statement, you would also put single quotes around [YEAR]. I test variables by displaying them in a text box to ensure I know what I'm comparing.

Re: Count Distinct IF (Conditions based on variables)

kaisdeco — Tue, 11 Aug 2020 21:23:04 GMT

Hi,

I'm working in this issue, and the sentence is working but, only with two conditions:

COUNT(DISTINCT(IF($(DIFF_YEAR) = 0 AND YEAR = 'CYTD', INFORCE_POL_NO))))

When I added this condition: AND MONTH = $(=MONTH_NO). It didn't work.

Thank you for your help.

Re: Count Distinct IF (Conditions based on variables)

Kushal_Chawda — Tue, 11 Aug 2020 21:29:06 GMT

try below

COUNT(DISTINCT(IF($(DIFF_YEAR) = 0 AND [YEAR] = 'CYTD' AND MONTH = $(MONTH_NO), CODE)))

Re: Count Distinct IF (Conditions based on variables)

kaisdeco — Wed, 12 Aug 2020 01:59:19 GMT

Hi,

I tried that but, when I used MONTH = $(MONTH_NO) the sentence does not recognize it. When I use MONTH = $(=MONTH_NO) the sentence recognize the month.

Re: Count Distinct IF (Conditions based on variables)

Kushal_Chawda — Wed, 12 Aug 2020 14:02:18 GMT

MONTH_NO is field? What values MONTH & MONTH_NO have?

Re: Count Distinct IF (Conditions based on variables)

kaisdeco — Wed, 12 Aug 2020 14:56:39 GMT

Hi Kush,

MONTH is a field and MONTH_NO is a variable containing the last month of a report I have uploaded. e.g It is currently 6 because I was working with the June data.

Re: Count Distinct IF (Conditions based on variables)

Kushal_Chawda — Wed, 12 Aug 2020 14:59:05 GMT

I can see that variable is having blank value. Can you check the variable value in KPI object? What is expression used in that variable?

Re: Count Distinct IF (Conditions based on variables)

kaisdeco — Wed, 12 Aug 2020 15:16:36 GMT

Hi Kush,

When I used MONTH = $(MONTH_NO) I didn't get the variable value, the value is in blank; however, when I use MONTH = $(=MONTH) I get the value. In the following example you can check it

Now, my issue is because when I use two conditions (DIFF_YEAR & YEAR) I have results but, when I use MONTH as well I did't get it.

Re: Count Distinct IF (Conditions based on variables)

Kushal_Chawda — Wed, 12 Aug 2020 15:31:36 GMT

Check if your month field has a number value? try with quotes '$(=MONTH_NO) ' or '$(MONTH_NO) '

Re: Count Distinct IF (Conditions based on variables)

kaisdeco — Thu, 13 Aug 2020 17:48:17 GMT

Hi guys,

Thank you for your help. I'd like to share with you that I got the info that I needed so far.

I used this sentence and it's working:

COUNT( DISTINCT( IF( $(DIFF_YEAR) = 0 AND YEAR = 'CYTD' AND MTH_NO = $(=MONTH_NO), INFORCE_POL_NO)))

I was checking the month values when you (Kush) asked me and I found that I was taking a bad field MONTH and I have to take MTH_NO.