Need help in rangesum expression

[chinnu123]

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!

[robert_mika]

Could you post your data/ app?

Feeling Qlikngry?

How To /Missing Manual(25 articles)

[harishkumarg]

Hi

Please find the attached QVW.

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

Regards

Harish

[john9inno]

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.

[Anil_Babu_Samineni]

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

[chinnu123]

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!

[chinnu123]

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

[rajpreeths]

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

[Anonymous]

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!!

[harishkumarg]

Hi Chinnu

Try this solution.

Regards

Harish