topic Re: If Expression to Set Analysis in QlikView

If Expression to Set Analysis

qlikwiz123 — Fri, 15 Nov 2024 23:26:15 GMT

=If(Year=Max(TOTAL <ID> Year), (Count(Distinct ID)) * 25)

This expression only gives me the result if I select a year. How can I change this to Set Analysis so that this expression provides an output for all the years?

Re: If Expression to Set Analysis

Taoufiq_Zarra — Wed, 16 Dec 2020 20:14:03 GMT

@qlikwiz123

Maye be :

count({$<Year = {"$(=Max(Year))"}>} Distinct ID)*25

or share your data and the expected output

Re: If Expression to Set Analysis

qlikwiz123 — Wed, 16 Dec 2020 20:30:55 GMT

This is just giving me 0's. I have a Total function and not sure how to use it in Set Analysis. The Data is huge and confidential, so not able to share the QVW

For Year 2022, since there is only one ID, it should be 1*25

For year 2027, since there are two IDs, it should be 2*25

ID YEAR Output

1 2022 25

2 2023 25

3 2021 25

4 2025 25

5 2030 25

6 2027 50

7 2027 50

Re: If Expression to Set Analysis

Taoufiq_Zarra — Wed, 16 Dec 2020 20:34:38 GMT

can you give just a dummy data and the expected.

ID,YEAR

1,2020

2,2019

...

i I understood correctly

dimension Year and Measure count(distinct ID) *25?

Re: If Expression to Set Analysis

qlikwiz123 — Wed, 16 Dec 2020 20:46:53 GMT

Please look into my previous response for the Output I am looking for.

Dimension is Year

Expression Measure is If(Year=Max(TOTAL <ID> Year), (Count(Distinct ID)) * 25)

If I just simply do Count(Distinct ID) *25, then it multiplies for every year

So I need to make sure Count(Distinct ID) * 25 is happening in those rows where the year is 2027 in this case.

Re: If Expression to Set Analysis

Taoufiq_Zarra — Wed, 16 Dec 2020 20:56:37 GMT

@qlikwiz123 not sur I understand, but

with this input

if Dimension is ID and Year

and measure is Count(total<YEAR> ID) *25

I can get the same output like :

Re: If Expression to Set Analysis

qlikwiz123 — Wed, 16 Dec 2020 20:58:51 GMT

To make it simple,

count({$<Year = {"$(=Max(Year))"}>} TOTAL <Year> Distinct ID) * 25

This Set Expression works when I select a year. I need a way to evaluate this even without making any selection on Year

Re: If Expression to Set Analysis

qlikwiz123 — Wed, 16 Dec 2020 21:07:35 GMT

I think the sample data was too clean and straightforward. It works with this simple set but not in my data.

The only thing that works is

count({$<Year = {"$(=Max(Year))"}>} TOTAL <Year> Distinct ID) * 25

This Set Expression works when I select a year. I need a way to evaluate this even without making any selection on Year