Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

set analysis without aggregation

Hi,

Is it possible to do a set analysis without aggregation?

Right now, I have a single table filled with data from different months. I want to calculate the data taken from the latest month only. Using the data from the latest month, i will check the letter of the alphabet. If it is A and more than 90 days has passed since i first found it, i will count the data. The same applies if the alphabet is B and more than 60 days has passed or if the alphabet is C and more than 30 days has passed. For now, i have come up with this expression:

Count(distinct(if([Status] = 'Open',

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

)))))

Putting this expression in a pie chart will show all the data from all the months together. However, I want to take the data from only the latest month, so i used set analysis. I have tried using set analysis to extract out the latest month of data only, however i do not need any set aggregation involved. Therefore is it possible to do set analysis without aggregation or do i have to make a new table for the latest month?

If it is possible to use set analysis without aggregation, then what am i doing wrong? In the expression below, i have added in the set analysis to the first part of the same expression but when i place the results in a table with all the other data, all i get is a -. Doing a max[Month] shows the name of my file so i think it has to do with the format or the aggregation. What am i doing wrong?

Count(distinct(if ({<[Month] = {'$(=Max([Month]))'}>}[Status] = 'Open'),

if({<[Month] = {'$(=Max([Month]))'}>}[Alphabet] = 'A',

if(Num(Today())>=({<[Month] = {'$(=Max([Month]))'}>}Num([First Found]+90)), ({<[Month] = {'$(=Max([Month]))'}>}[data]))))

For example:

I have the following data model:

MonthAlphabetStatusFirst Founddata
FebAClosed02/05/2017apple
FebBOpen05/07/2017orange
AugCOpen19/12/2016banana
JunBOpen25/02/2017pineapple

Aug

AClosed07/03/2017banana
AugBOpen14/07/2017pineapple
JunCClosed12/09/2016apple
AugBOpen15/01/2017apple

As i will be taking only the data from the latest month, rows 3, 5, 6 and 8 will be the ones I'm focusing on. Next, I am focusing on the status, where if it is 'Open' then i will include it. Therefore, i will now only look at rows 3, 6 and 8 . Next, depending on the alphabet, i will be counting the time from today to the first found date. Now, i will have rows 3 and 8 left. (90 days on A, 60 days on B, 30 days on C). Lastly, i will count the number of data left, which is 2.

Hope it now makes sense.

Thanks!

1 Solution

Accepted Solutions

Re: set analysis without aggregation

Hi,

Not sure what you are looking but give it a try with below expression.

Count({<[Month] = {'$(=Max([Month]))'}>}distinct(if([Status] = 'Open',

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

)))))


Regards,

Kaushik Solanki

8 Replies

Re: set analysis without aggregation

Perhaps like this:


Count({<[Status]={'Open'},[Month]={"$(=Max([Month]))"}>}distinct(

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

))))


talk is cheap, supply exceeds demand

Re: set analysis without aggregation

Hi,

Not sure what you are looking but give it a try with below expression.

Count({<[Month] = {'$(=Max([Month]))'}>}distinct(if([Status] = 'Open',

if([Alphabet] = 'A', if(Num(Today())>=Num([First Found]+90), [data]),

if([Alphabet] = 'B', if (Num(Today())>=Num([First Found]+60), [data]),

if([Alphabet] = 'C', if (Num(Today())>=Num([First Found]+30), [data]))

)))))


Regards,

Kaushik Solanki

vinieme12
Esteemed Contributor II

Re: set analysis without aggregation

Please post some sample data! and expected output

Set analysis is possible with aggregation functions only and you will only use set analysis when you have any conditions/filters to apply in your calculations.

It’s all Aggregations

Not applicable

Re: set analysis without aggregation

Hi,

using the expression still gives me a -. I have updated the question to clarify on what i am looking for.

Thanks!

Not applicable

Re: set analysis without aggregation

Hi,

using the expression still gives me a -. I have updated the question to clarify on what i am looking for.

Thanks!

Not applicable

Re: set analysis without aggregation

Hi,

I have updated the question to clarify on what i am looking for. Hope it now makes sense.

Thanks!

Re: set analysis without aggregation

May be this

=Sum({<MonthNum = {$(=Max(MonthNum))}, Status = {'Open'}>}

If(Alphabet = 'A' and [First Found] < Today() - 90, 1,

If(Alphabet = 'B' and [First Found] < Today() - 60, 1,

If(Alphabet = 'C' and [First Found] < Today() - 30, 1))))

Capture.PNG

Not applicable

Re: set analysis without aggregation

Hey,

Can't seem to edit my previous post. I relooked at the expression again and found out that i made a couple mistakes when copying the expression over to my app. After fixing those errors they seem to work now.

Thanks!

Community Browser