topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

s2016 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to plot a bar chart using Facebook, Google, etc. as my dimension and aggregate the data to Monthly level and show just one month of data.

The default month has to be one month after the current month, ie, July for this month (June). Otherwise if user makes a selection of any other Month and Year then show the data for that month.

I am looking for some help in writing the expression for this.

Re: Set Analysis Expression

antoniotiman — Wed, 28 Jun 2017 06:07:09 GMT

Hi,

see Attachment.

Regards,

Antonio

Re: Set Analysis Expression

sujeetsingh — Wed, 28 Jun 2017 06:20:14 GMT

Now the solution for your data set and visualization is as :

1. Using set analysis for Point of time and comparing the calculation over present month and previous month.

2. Aggregating the expression on Group level.

Re: Set Analysis Expression

balar025 — Wed, 28 Jun 2017 07:58:18 GMT

Hi,

Use below script and function inside a script,

CrossTable(Dimension, Data,4)

LOAD Week,

Month(Week) as Month,

Year(Week) as Year,

Date(MakeDate(Year(Week),Month(Week),01),'MM-YYYY') as MonthYear,

[Google-TID],

[Google-BP],

Akamai,

Netflix,

Apple,

Facebook,

Microsoft,

Amazon,

Edgecast,

Limelight

FROM

(ooxml, embedded labels, table is [Traffic (Mbps) W F]);

variable vMonth = =Date(if(GetPossibleCount(MonthYear)>1,AddMonths(Max(MonthYear),-1),Max(MonthYear)),'MM-YYYY')

Expression : sum({<MonthYear={'$(vMonth)'}>}Data)

Re: Set Analysis Expression

s2016 — Wed, 28 Jun 2017 23:56:36 GMT

Thanks Antonio!

Although your approach gives me some insight on how this could be achieved but I want a bar graph and on my dimensions I just want to have the "Groups" and not the Month and Year.

I am still trying to figure out how that could be done.

Re: Set Analysis Expression

antoniotiman — Thu, 29 Jun 2017 03:37:09 GMT

See Attachment