Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Thank you @vinieme12 for your quick reply....It worked !!! 😊
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.
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
)
))
What exactly are you trying to calculate here? doesn't make sense avg / avg ?
Its (avg for each customer)/Total Avg
Anyways its working fine now after adding TOTAL in correct place 🙂