topic Re: Need help in rangesum expression in QlikView

Need help in rangesum expression

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

I am having tickets from april 2016 to present, I need to show the distinct tickets count in percentages in bar chart for every month.

For Example: In April we have total 100 tickets and in April month resolved tickets are 70, Now the formula is

= (resolved tickets/Total tickets)*100

Coming to May month we have total 150 tickets and in may month resolved tickets are 100, Now the formula is

= (April+May resolved tickets)/(April+May total tickets)*100

i.e.. (70+100)/(100+150)*100

same as

Coming to June month we have total 170 tickets and in June month resolved tickets are 120, Now the formula is

= (April+May+June resolved tickets)/(April+May+June total tickets)*100

i.e.. (70+100+120)/(100+150+170)*100

I am using rangesum in my set expression, but I am missing some where...Can any one assist in this?

Thanks!

Re: Need help in rangesum expression

robert_mika — Thu, 29 Sep 2016 03:53:14 GMT

Could you post your data/ app?

Feeling Qlikngry?

How To /Missing Manual(25 articles)

Re: Need help in rangesum expression

harishkumarg — Thu, 29 Sep 2016 04:48:56 GMT

Please find the attached QVW.

I have used RangeSum and I am able to get the value you are expecting.

Regards

Harish

Re: Need help in rangesum expression

john9inno — Thu, 29 Sep 2016 05:26:25 GMT

easiest way to this without seeing data i can think about is ....

Make 2 expressions as

resolved tickets = SUM(resolved tickets) -> assign it to be accumulative

Total tickets = Sum(Total tickets) -> Also make it Accumulative

both of them turn to invisible.

and final expression as resolved tickets/Total tickets

you might need to have month as dimension.

Re: Need help in rangesum expression

Anil_Babu_Samineni — Thu, 29 Sep 2016 05:37:28 GMT

chinnu k wrote:

I am having tickets from april 2016 to present, I need to show the distinct tickets count in percentages in bar chart for every

For this, take month as dim

Expression is this?

Count( distinct tickets)

For another queries, you must share scrambled data so that we can help you

Re: Need help in rangesum expression

chinnu123 — Thu, 29 Sep 2016 16:27:25 GMT

Hi Harish,

The qvw you shared is almost what we are expecting, even we have a small problem. So that is the reason I am sharing some demo data.

Here is the conditions

Take dimension as Month

Expression Condition:

if(resolveddate<=target date), sum(tickets) / total(resolveddate)

please use the attached data and try in bar chart.

Thanks alot!

Re: Need help in rangesum expression

chinnu123 — Sun, 02 Oct 2016 23:09:57 GMT

Hi Harish,

In the above scenario You have used Rangesum in the same way can you please tell me how to use rangecount

because I am having tickets so I cannot use sum i need to use count for counting the number

Thanks,

Chinnu

Re: Need help in rangesum expression

rajpreeths — Mon, 03 Oct 2016 12:28:29 GMT

Hello Chinnu,

I have tried to derive the solution in the bar chart with Month as dimension. The solution is working fine but for your condition if (resolveddate<=target date) , chart cannot show ( I think ) correct data without having date field as dimension . (Since One month has many date fields - [One to Many] chart does not know to which date, the comparison should happen) . Hence I suggest you to do necessary changes using group by , order by, rangesum in the script part itself ( for having the condition if(resolveddate<=target date)) .

If you find any difficulties, kindly share your application with scrambled data so that we can help you further.

Please refer the attachment for partial solution ( without your condition ) with month as dimension).

Note:

-> While using Rangesum and above functions in the chart, please ensure dimensions are sorted as the load order in order to have accurate results.

In this application, I have sorted ' Month ' field based on LOAD ORDER.

Rajpreeth S

Re: Need help in rangesum expression

Anonymous — Mon, 03 Oct 2016 13:42:28 GMT

Hi Chinnu, use this expression:

Add this code when you load your data on script:

Year(Resolveddate) as ResolvedYear,

Month(Resolveddate) as ResolvedMonth,

Month(Targetdate) as TargetMonth,

Year(Targetdate) as TargetYear,

Then your expression my be this:

count(DISTINCT {<ResolvedYear={$(=Max(ResolvedYear))}, ResolvedMonth={'<=$(=Max({<ResolvedYear={$(=Max(ResolvedYear))}>}ResolvedMonth))'} >} if(Resolveddate<=Targetdate,Tickets))

/count(DISTINCT {<TargetYear={$(=Max(TargetYear))}, TargetMonth={'<=$(=Max({<TargetYear={$(=Max(TargetYear))}>}TargetMonth))'} >} Tickets)

You don't need rangesum for this, this is a YTD.

Regards!!

Re: Need help in rangesum expression

harishkumarg — Mon, 03 Oct 2016 16:34:16 GMT

Hi Chinnu

Try this solution.

Regards

Harish