Issue while displaying the chart

[Pragyan]

Hi All,

I have one expression that returns the sum of transactions count based on filtered date and all other filters are ignored. 

This returns correct results. 

sum({1<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count)

In other expression I am getting transactions for each customer based on filter with expression 

This also returns correct results. 

aggr(sum({<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count)
,[ts_date_entered],[customer]))

Finally in another expression I am trying to display one bar chart with customer[dimension] and measure is the individual transaction share(%) with expression

(aggr(sum({<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count)
,[ts_date_entered],[customer])))/(sum({1<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count))

But here I am not getting proper result and all customers showing with 100%

NOTE:- If I am putting the hard coded value of second expression (ex-if I am getting result of  (sum({1<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count) as 108863) Then getting correct percentage of share.

(aggr(sum({<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count)
,[ts_date_entered],[customer])))/108863

Could anyone one please help me on this?

Thanks,

Pragyan

[vinieme12 · Accepted solution]

sum({<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}ts_txn_count)

/

sum({<Year=, Quarter=, Month=, Week=, date=, ts_date_entered={"$(=(Max(ts_date_entered)))"}>}TOTAL ts_txn_count)

[Pragyan]

Thank you @vinieme12  for your quick reply....It worked  !!! 😊

[Pragyan]

Hi, @vinieme12 I am facing similar kind of issue when using below expression for MTD marketshare calculation:

(avg(
{<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>}
aggr(
sum({<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>} ts_txn_count),
[t101_mm_id-ts_issuer_id], [ts_date_entered]
)
))/(avg(
{<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>}
aggr(
sum({1<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>}total ts_txn_count),
ts_date_entered
)
))

Hope you can help in this as well. I tried with Total but not working.

[Pragyan]

Sorry, I made a mistake in my previous expression. Here as I am taking the average total so I should be putting the Total for aggregated value not for sum total. And my expression worked perfectly fine. As I am new to Qlik sense so sometime making mistake in basics. So here goes the updated expression:

(avg(
{<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>}
aggr(
sum({<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>} ts_txn_count),
[t101_mm_id-ts_issuer_id], [ts_date_entered]
)
))/(avg(
{<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>} Total
aggr(
sum({1<Year=, Quarter=, Month=, Week=, date=, [ts_date_entered]={">=$(=MonthStart(Max(ts_date_entered)))<=$(=Max(ts_date_entered))"}>} ts_txn_count),
ts_date_entered
)
)) 

[vinieme12]

What exactly are you trying to calculate here? doesn't make sense avg / avg ?

[Pragyan]

Its (avg for each customer)/Total Avg

[Pragyan]

Anyways its working fine now after adding TOTAL in correct place 🙂