Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm migrating an application to QlikSense. So far it has been a smooth process except migrating of a part of the application.
Currently we use sql queries to generate the specified column.
The query used looks like the one below.
SELECT Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2) AS Expr1
FROM (SELECT Sum(Invoice.GrossValue_LC) AS SumOfGrossValue_LC, Sum(Invoice.COGS_LC) AS SumOfCOGS_LC,
PH9.PH3, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr)
INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH3, Invoice.BillingYear
HAVING (((PH9.PH3)='" & PH6 & "' ) AND ((Invoice.BillingYear)='" & BillingYear & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);
The bold element in the "Having" clause in the SQL query will be dynamically selected by the User in the UI.
For example:Billing year=2011 and PH6=231546.
The above query results a percentage.
I do not want to use a SQL or LOAD statement as above. I instead want to use some QlikSense functions (Like Aggr()) and set analysis which will help in achieving the same result as the above query does.
Any suggestions please with an example?
don't use the SQL queries, instead create a datamodel in Qlik. The power of Qlik is in the datamodel and it's associative engine. So just load Material as a table, invoices as a table, create a calendar table, and create a key for:
Calendar <=> Invoice
Material <=> Invoice
and do the sum and selections on year and material in qlik
can u brief little more on how to create calendar table?
Try to create something like this:
with this model/script you can create your visualizations. you can filter on year, material etc. and have functions like sum(qty) an sum(amount) by year/month/date/material etc.
GL