Sql Keyword "Over" does not work, are their alternatives?


1 comment

  • Avatar
    Hamza Jap-Tjong

    Would it be possible to create the subtotals in Sisense instead of the elasticube? I think it would be possible. 


    I think there are 2 possible options (if I understand your question correctly);

    1. Use the import-query option in the ElastiCube. This makes it possible to use the query-language that the datasource uses instead of the SQL-language that Sisense uses. Documentation

    2. If you want to do it in the Elasticube you could do a nested join statement. Something like; 

    (count( s.[Transaction ID])) as Sales,
    round((count( s.[Transaction ID])*s.[Amount Sales Net]),3) as Revenue,
    s.[Amount Sales Net] as Price,
    s.[Clearing Method],
    s.[Debitors Description] as Payment_Type,
    s.[CC type],
    s.[Currency Amount Sales]

    from [Data]s

    left join (select groupbyfield, sum(revenue) from Data group by groupbyfield ) a on a.groupbyfield = s.joinfield
    group by s.[Clearing Method], s.[Amount Sales Net], s.[Debitors Description], s.[Tariff], s.[CC type],s.[Currency Amount Sales]


    You should alter the nested statement to suit your needs. 



    Comment actions Permalink

Please sign in to leave a comment.