topic Re: Set Analysis in QlikView

Set Analysis

vanderson009 — Sat, 28 Jul 2018 15:37:24 GMT

I have below data,

I need to build set analysis as to show only those sales for last two month and whose quantity is greater than 100. I have highlighted expected output.

Thanks,

Villyee

Re: Set Analysis

sunny_talwar — Sat, 28 Jul 2018 16:09:51 GMT

May be like this

Dimension

MonthYear

Product

Expression

Sum({<MonthYear = {"$(='>=' & Date(MonthStart(Today(), -1), 'MMM-YYYY') & '<' & Date(MonthStart(Today(), 1), 'MMM-YYYY'))"}>}Aggr(If(Sum(Quantity) > 100, Sum(Quantity)), MonthYear, Product))

Sum({<MonthYear = {"$(='>=' & Date(MonthStart(Today(), -1), 'MMM-YYYY') & '<' & Date(MonthStart(Today(), 1), 'MMM-YYYY'))"}>}Aggr(If(Sum(Quantity) > 100, Sum(Sale)), MonthYear, Product))

Here I am assuming that MonthYear is created in the script using a format like this

Date(MonthStart(Date), 'MMM-YYYY') as MonthYear

Re: Set Analysis

vanderson009 — Sat, 28 Jul 2018 17:24:24 GMT

Hi Sunny,

I tried expression that you suggested but my bad both are not working and modified little bit.

Can you please let me know why below expression is not working,

Sum({<MonthYear={">=$(=Date(MonthStart(Today(),-1),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"}>} Aggr(If(Sum(Quantity) > 100, Sum(Sales)), MonthYear, Product))

Thanks,

Villyee

Re: Set Analysis

vanderson009 — Sat, 28 Jul 2018 17:32:11 GMT

PFA dummy application.

Thanks

Re: Set Analysis

sunny_talwar — Sun, 29 Jul 2018 10:27:28 GMT

Change the MonthName function to Date(MonthStart()) function in the script

LOAD *, Date(MonthStart(Date), 'MMM YYYY') as MY INLINE [

Date, Product, Quantity, Sales

1/1/2018, A, 80, 1

1/1/2018, B, 110, 1

1/1/2018, C, 90, 1

2/1/2018, A, 80, 1

2/1/2018, B, 70, 1

3/1/2018, A, 100, 1

3/1/2018, B, 110, 1

3/1/2018, C, 90, 1

4/1/2018, B, 80, 1

5/1/2018, B, 90, 1

5/1/2018, C, 70, 1

6/1/2018, A, 80, 1

6/1/2018, B, 110, 1

6/1/2018, C, 120, 1

6/1/2018, D, 115, 1

7/1/2018, A, 100, 1

7/1/2018, C, 90, 1

];

and reloaded the app to get this