SQL query into Expression

[Nikhil2725]

Hi All,

I want to convert the below SQL statement into Expression.

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr)
INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

I'm using a filter for the Billing Year.

What ever the user selects year from the UI that value should come in "invoice.BillingYear" like wise for PH6 also

How can I achieve it??

[Nikhil2725]

Any suggestions for the above query????

Im stuck in that:(

[Nikhil2725]

Any Ideas??

[Nikhil2725]

It Means I have to use dynamic selection on Having Clause...

HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "')))

Here in the above... user will select the PH6 Number and Billing year dynamically through the filters in the UI.

[sunny_talwar]

Would you be able to provide a sample where we can check this out?

[Nikhil2725]

Hi Sunny,

Please find the below table...

Customer #	Price Group	Customer name	Location	Region	Division	PH #	PH name	Quantity	GM VG	GrossValue_LC	COGS
512547	Y1	test customer 439	City 439	56	KKS	231546	BV 546	2	45%(This is desired output I needs to get)	4972	2262.15

In the above table I need to calculate GM VG column.

For that in the backend we have use the following query.

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr) INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);

Here in the above query in the HAVING condition &PH& and &year& will be selected by the Users in the UI..

In the above example table User has selected 231546 PH and 2010 billing year..

[sunny_talwar]

I believe Qlik Sense by default will show based on your selections... if you have selected those two... you should automatically see those rows where the condition is met.

[Nikhil2725]

Hi Sunny,

If i execute that Query then only I will get that desired output....

[sunny_talwar]

Which query?

[Nikhil2725]

Hi Sunny,

The below query I need to run 

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.PH6, Invoice.BillingYear
FROM (Invoice INNER JOIN Material ON Invoice.MaterialNr = Material.MaterialNr) INNER JOIN PH9 ON Material.PH9 = PH9.PH9
GROUP BY PH9.PH6, Invoice.BillingYear
HAVING (((PH9.PH6)='" & PH & "' ) AND ((Invoice.BillingYear)='" & year & "'))) AS ord1
GROUP BY Round(([SumOfGrossValue_LC]-[SumOfCOGS_LC])/[SumOfGrossValue_LC],2);