topic Re: Set analysis question in QlikView

Set analysis question

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

Hi all,

In Variable Overview i have defined the following variable:

e_WP = sum({<LineType = {'Written'}, $(=Replace('$1', ';', ','))>}WP)

Now i want to use this variable in expressions like this

$(e_WP) for current selection which is working

if i use $(e_WP(MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month = )) i get the correct result for YTD

How should i define a new variable like vSetYTD and use as $(e_WP($(vSetYTD)))?

Re: Set analysis question

sunny_talwar — Wed, 05 Oct 2016 16:33:52 GMT

May be like this:

vSetYTD: MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

vSetYTD: =MonthID = {"<=$(=Max(MonthID))"}; Year = {$(=Max(Year))}; Month =

and then this:

$(e_WP($(=vSetYTD)))

$(e_WP($(vSetYTD)))

Re: Set analysis question

tunoi — Thu, 06 Oct 2016 07:33:46 GMT

ty, it worked !

Re: Set analysis question

tunoi — Thu, 06 Oct 2016 07:41:24 GMT

Now next part of my question.

I will have one year and one month selected. in one chart with year_month dimension i want to display for the last 12 month the YTD for the each of the Year_Month.

How should i define my set analysis variable?

Re: Set analysis question

sunny_talwar — Thu, 06 Oct 2016 17:11:25 GMT

Each of the Year_Month? Do you have a sample of what you are wanting to do?

Re: Set analysis question

tunoi — Fri, 07 Oct 2016 07:06:35 GMT

I have the following table:

Year	Month	Year_Month	MonthID	WP
2015	1	2015_1	1	10
2015	2	2015_2	2	20
2015	3	2015_3	3	5
2015	4	2015_4	4	15
2015	5	2015_5	5	23
2015	6	2015_6	6	21
2015	7	2015_7	7	12
2015	8	2015_8	8	45
2015	9	2015_9	9	46
2015	10	2015_10	10	32
2015	11	2015_11	11	21
2015	12	2015_12	12	5
2016	1	2016_1	13	10
2016	2	2016_2	14	15
2016	3	2016_3	15	20
2016	4	2016_4	16	21
2016	5	2016_5	17	24
2016	6	2016_6	18	45
2016	7	2016_7	19	56
2016	8	2016_8	20	34
2016	9	2016_9	21	23
2016	10	2016_10	22	12
2016	11	2016_11	23	18
2016	12	2016_12	24	6

So if the user select Year = 2016 and Month = 5 my output should be:

Year Month	YTD(WP)
2015_6	94
2015_7	106
2015_8	151
2015_9	197
2015_10	229
2015_11	250
2015_12	255
2016_1	10
2016_2	25
2016_3	45
2016_4	66
2016_5	90

Re: Set analysis question

sunny_talwar — Fri, 07 Oct 2016 17:06:49 GMT

May be using an expression like this:

=RangeSum(Above(Sum({<Year, Month, Year_Month, MonthID>}WP), 0, Only({<Year, Month, Year_Month, MonthID>}Month)))

Avg({<Year_Month = {"$(='>' & Date(AddMonths(Max(Year_Month), -12), 'YYYY_M') & '<=' & Date(Max(Year_Month), 'YYYY_M'))"}, Year, Month, MonthID>}1)

I have added the breakdown of the two expressions for you to see why it is working the way it is working