topic Re: Set Analysis in QlikView

Set Analysis

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

Hi ExpertiseLogic:

WeekStart current previous difference(currentweek-previousweek) metric

12-march-2017 8 5(this value is 5-march-current week data) 3

19-march-2017 10 8 2

26-march-2017 15 10 5

Out Put Table should like this :

Date Tickets

12-march-2017 3

19-march-2017 2

26-march-2017 5

could you please suggest me how can i achieve solution for this?

Thanks in Advance

Re: Set Analysis

Stoyan_Terziev — Thu, 21 Nov 2019 11:04:01 GMT

Hi John,

I think your desired expression for the last week data only should look like this:

Count({<Year*={">2015"},Status={'OpenTicket'}, Weekstart ={"=$(=Max(Weekstart))"}>} Key)

Keep in mind this will work only if you have the Weekstart field in your default date format. Otherwise you will have to set the format individually.

PS: I'm perplexed by this par: Sum({1<Year*={">2015"}>}0)

This should basically do + 0. Most likely it was placed there so it will force those weeks in 2016 onward with 0es to appear even if no Key values are associated.

Kind regards,

S.T.

Re: Set Analysis

smilingjohn — Thu, 21 Nov 2019 11:11:17 GMT

This is not working

Re: Set Analysis

sunny_talwar — Thu, 21 Nov 2019 14:18:14 GMT

Shouldn't it be just this?

Count({<Year*={">2015"},Status={'OpenTicket'}>}Key) + Sum({1<Year*={">2015"}>}0)

You are just non-cumulating the cumulative distribution... isn't it?

Re: Set Analysis

smilingjohn — Thu, 21 Nov 2019 14:38:50 GMT

Hi Sunny sir ,

Hi Sir i want to show a table with the difference of the two expression . And the new table will should show the cumulative with always Subtracting the current week to previous week .

I used your expression but that is not giving me the desired result , Need your help on this

Thanks

Re: Set Analysis

sunny_talwar — Thu, 21 Nov 2019 14:43:47 GMT

You can just call me Sunny.

I am not sure I understand what is going on? Can you share an example showing the issue you have?

Re: Set Analysis

smilingjohn — Fri, 22 Nov 2019 03:32:08 GMT

Confused

Thanks

Re: Set Analysis

sunny_talwar — Thu, 21 Nov 2019 16:08:33 GMT

I don't see any attachment with the above post?

Re: Set Analysis

smilingjohn — Fri, 22 Nov 2019 03:32:39 GMT

No luck

Re: Set Analysis

sunny_talwar — Thu, 21 Nov 2019 20:26:01 GMT

Are you looking for this may be

RangeSum( RangeSum(Before(TOTAL Count({<Year *= {">2015"}, Status = {'OpenTicket'}>}Key) + Sum({1<Year *= {">2015"}>}0), 0, ColumnNo(TOTAL))), -Before(TOTAL RangeSum(Before(TOTAL Count({<Year *= {">2015"}, Status = {'ClosedTicket'}>}Key) + Sum({1<Year *= {">2015"}>}0), 0, ColumnNo(TOTAL)))) )

Re: Set Analysis

smilingjohn — Fri, 22 Nov 2019 01:09:58 GMT

Hi sunny

In the second of closed ticket set analysis where is last week expression ?

This will just subtract th ope tickets - closes tickets which is partially correct .but my requirement is to subtract last week in closed ticket from open tickets .

Re: Set Analysis

smilingjohn — Fri, 22 Nov 2019 02:43:56 GMT

Thanks a lot Sunny Sir,

This worked for you just rock

Re: Set Analysis

sunny_talwar — Fri, 22 Nov 2019 11:50:12 GMT

Glad it worked...you understand the logic now?