Hello,
I have 2 different questions (queries) to visualize data related to total sum of each company and percentage of that sum total.
Query:
[
{
"$match": {
"$expr": {
"$ne": [
{
"$substrCP": [
"$GST Identification Number (GSTIN)",
2,
10
]
},
""
]
},
}
},
{
"$match": {[[
"Customer Name": {{Customername}}
]]}
},
{
"$match": {[[
"Invoice Date": {
"$gte": {{startdate}},
"$lte": {{enddate}}
}
]]}
},
{
"$group": {
"_id": {
"pan": {
"$substrCP": [
"$GST Identification Number (GSTIN)",
2,
10
]
},
"customer_name": "$Customer Name",
"invoice_date": "$Invoice Date"
},
"gsts": {
"$addToSet": "$GST Identification Number (GSTIN)"
},
"sum": {
"$sum": "$Total"
}
}
},
{
"$sort": {
"_id.pan": -1
}
},
{
"$group": {
"_id": "$_id.pan",
"first_customer_name": {
"$first": "$_id.customer_name"
},
"gsts": {
"$addToSet": "$gsts"
},
"sum": {
"$sum": "$sum"
},
"invoice_date": {
"$addToSet": "$_id.invoice_date"
}
}
},
{
"$match": {[[
"$expr": {
"$gt": [
{
"$size": "$gsts"
},
{{BOOL}}
]
}
]]}
},
{
"$sort": {
"sum": -1
}
},
{
"$group": {
"_id": null,
"totalSum": {
"$sum": "$sum"
},
"records": {
"$push": {
"pan": "$_id",
"first_customer_name": "$first_customer_name",
"gsts": "$gsts",
"sum": "$sum",
"invoice_dates": "$invoice_date"
}
}
}
},
{
"$unwind": "$records"
},
{
"$project": {
"_id": false,
"pan": "$records.pan",
"first_customer_name": "$records.first_customer_name",
"gsts": "$records.gsts",
"sum": "$records.sum",
"invoice_dates": "$records.invoice_dates",
"percentageOfTotal": {
"$multiply": [
{
"$divide": ["$records.sum", "$totalSum"]
},
100
]
}
}
},
{
"$limit": {{total_number}}
}
]
How to actually merge these two charts one with percentage total and another with summation of total with one common dimension (invoice dates) .