Cumulative data using infinity uql

Grafana
1

Hi, I have json data where i want to have month wise cumulative count of all the new customers to the website on monthly basis from below example data:

[{"CustomerID": "Lalith_k01","AccountType": "silver","login": "2024-12-30"},
{"CustomerID": "Prakash_pmb","AccountType": "gold","login": "2025-01-29"},
{"CustomerID": "Nishanth_msk","AccountType": "premium","login": "2025-02-15"},
{"CustomerID": "Rony_bld","AccountType": "silver","login": "2025-01-03"},
{"CustomerID": "Lalith_k01","AccountType": "silver","login": "2024-12-15"},
{"CustomerID": "Prakash_pmb","AccountType": "gold","login": "2025-03-19"},
{"CustomerID": "Lalith_k01","AccountType": "premium","login": "2025-01-22"},
{"CustomerID": "Lalith_k01","AccountType": "silver","login": "2024-12-09"},
{"CustomerID": "Prakash_pmb","AccountType": "gold","login": "2025-03-07"},
{"CustomerID": "Nishanth_msk","AccountType": "premium","login": "2025-03-15"},
{"CustomerID": "kamal_std","AccountType": "silver","login": "2024-12-30"},
{"CustomerID": "kamal_std","AccountType": "gold","login": "2025-01-29"},
{"CustomerID": "Thirunath_dkm","AccountType": "premium","login": "2025-02-15"},
{"CustomerID": "Rony_bld","AccountType": "silver","login": "2025-02-13"},
{"CustomerID": "Lalith_k01","AccountType": "silver","login": "2024-12-01"},
{"CustomerID": "Prakash_pmb","AccountType": "gold","login": "2025-02-19"},
{"CustomerID": "Lalith_k01","AccountType": "premium","login": "2025-03-22"},
{"CustomerID": "Lalith_k01","AccountType": "silver","login": "2024-12-09"},
{"CustomerID": "Prakash_pmb","AccountType": "gold","login": "2025-03-07"},
{"CustomerID": "Nishanth_msk","AccountType": "premium","login": "2025-03-15"}]

Could you help me with the uql query and if required the transformations please?

2

Just to clarify, Lalith would only appear as a new customer in Dec2024 and then never again because that person already logged in?

3

Yes @grant2 . That’s correct. So, in short this is the expectation:
2024-12 — 2 – Lalith_k01, kamal_std
2025-01 — 4 – Rony_bld, Prakash_pmb
2025-02 — 6 – Nishanth_msk, Thirunath_dkm
2025-03 — 6 – NONE

4

Hi @itroyalsudheer

This is a tricky one! I put your data into a Play dashboard here so others can experiment with it. I think we will have to incorporate jsonata to get the results you want. ChatGPT or other AI may help you!

I also tried just performing some Grafana transformations to get distinct counts, but count not find a way to account for those situations where the user appears in a prior month.

@yosiasz @dawiddebowski @sowdenraymond @jangaraj — Any ideas?

5

please explain why Lalith_k01 is 2 in 2024-12 and Rony_bld 4 when that person only appears 2 times in the sample data.

6

Hello, Lalith_k01 is the first time customer in december 2024. And in that month there are not logins (lalith_k01 and kamal_std). In the month of Jan 2025 there are other 2 new logins (Rony_bld and prakash_pmb) and cumulative count for jan 2025 will be december 2024’s 2 count + jan 2025’s 2 count = 4 overall.The next month count will be dec’24+jan’25+feb’25=2+2+2. But in March 2025 there are no new logins so the count remains 6.

1 Like
7

it would require some serious functional programming in jsonata something like this

(
    $cumulus := [];
    $addIfnotExists := function($key, $value) {
        $k := 0
    };
    
   $map($, function($v, $i, $a) {
/* add if customer and YYYY-MM do not exist, do it inline ternary or via a function*/
        $cumulus.map(function($v) { 
            ($v.CustomerID = $v.CustomerID and $v.login = $v.login) ? true : false 
        })
    });
)

/*afterwards do your cumulative aggregation*/
1 Like
8

Another idea I had was to use Node-RED as an intermediary, i.e.

  1. receive the live JSON feed every n minutes (or days, if that makes more sense)
  2. use Python, Javascript, etc. to do the programming that @yosiasz mentions (might be easier than JSONata, but to each his own)
  3. publish the simplified data in a .CSV or .JSON that Infinity can read and display in Grafana
9 
    $data := $distinct($.{
        'CustomerID': CustomerID,
        'login' : $substring(login,0,7)
    });

generates a distilled version and removes dups. then do the $reduce approach. this one is a :bear: !