Month and Year from Date Field

[djsampat]

Hi All,

I need some help. I have been trying to extract the Year and Month to match our Financial Year and Period with the script below.

In SQL it works but in Qlikview it seems to run once, instead of for each line.

I need to make the Financial Year start from 1st July of each year being equal to the first period(month).

Edited: From the responses, i have added a PeriodYear field and added as a list box, but I would still need to somehow automate it to be selected. eg. if i select Year 2015 and Period 5 (Being November 2014), then it should link with 052015 in the results.

SELECT T1.NUM_0,T1.SIDLIN_0,T2.ACCDAT_0,MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY,

"Period"&"FIY" as PerYr

FROM SINVOICED T1, SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

[vinay_hg]

hi, give 2 input box to capture period, put this 2 values in variables then compare the date between the given values in varibles in expression or if u take dimention as date with dimention.(claculated dimension). by this u can provide user to select the required dynamic periods for selection.

[ashfaq_haseeb]

Hi,

try this

MonthName(Yourdatefield) as MonthYear.

let me know  if that worked.

Regards

ASHFAQ

[its_anandrjs]

Hi,

After getting table into the Qlikview you can concatenate this two fields

Load

[Month] & [Year] as [Month Year]

From Location;

And if format problem then convert to its appropriate date format

Regards

Anand

[anbu1984]

Can you post QV script you tried

[djsampat]

Hi Vinay, can you give more detail or modify my SQL query to show what you are saying.

On the Sales Detail screenshot, I am expecting to select only Year and Period and require all the Sales Detail for that period. I am not able to add more selections, as it would affect other Tabs.

[djsampat]

View below

InvoiceLines:

LOAD

    "CPY_0" AS [Company],

    "NUM_0" as SalesInvNum,

  "SIDLIN_0" as InvLine,

  if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("NETPRI_0"*"QTY_0")*-1, "NETPRI_0"*"QTY_0")) as Sell,

  if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("CPRPRI_0"*"QTY_0")*-1, "CPRPRI_0"*"QTY_0")) as Cost,

  "GROPRI_0",

  "NETPRI_0",

  "CPRPRI_0",

  "ITMREF_0" as Product,

    "ITMDES1_0" as ProdDesc,

    round("QTY_0",0.01) as QTY_0,

    round("PFM_0",0.01) as PFM_0,

    0 as Forecast,

    round(applymap('SOQTY',"SOHNUM_0"&"SOPLIN_0", QTY_0),0.01) as OrderedQty,

    0 as Freight,

    "ACCDAT_0" as InvAccDate,

    "Month" as InvMonth,

    "Year" as InvYear,

    "Period",

    "FIY"

    ;

SQL

SELECT T1.*, T2.INVTYP_0, T2.ACCDAT_0, MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY

FROM x3live.GBILIVE.SINVOICED T1, x3live.GBILIVE.SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

[anbu1984]

"In SQL it works but in Qlikview it seems to run once, instead of for each line." -- Can you explain your issue with example and expected output

[djsampat]

Hi Ashfaq,

It crashed my qvw with the following error and Out of Object Memory after 5 mins of being frozen.

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

[jagan]

Hi,

You need to remove Double quotes for the below two lines

"Month" as InvMonth,

    "Year" as InvYear,

Try this script

InvoiceLines:

LOAD

    "CPY_0" AS [Company],

    "NUM_0" as SalesInvNum,

  "SIDLIN_0" as InvLine,

  if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("NETPRI_0"*"QTY_0")*-1, "NETPRI_0"*"QTY_0")) as Sell,

  if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("CPRPRI_0"*"QTY_0")*-1, "CPRPRI_0"*"QTY_0")) as Cost,

  "GROPRI_0",

  "NETPRI_0",

  "CPRPRI_0",

  "ITMREF_0" as Product,

    "ITMDES1_0" as ProdDesc,

    round("QTY_0",0.01) as QTY_0,

    round("PFM_0",0.01) as PFM_0,

    0 as Forecast,

    round(applymap('SOQTY',"SOHNUM_0"&"SOPLIN_0", QTY_0),0.01) as OrderedQty,

    0 as Freight,

    "ACCDAT_0" as InvAccDate,

    [Month] as InvMonth,

   [Year] as InvYear,

    "Period",

    "FIY"

    ;

SQL

SELECT T1.*, T2.INVTYP_0, T2.ACCDAT_0, MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY

FROM x3live.GBILIVE.SINVOICED T1, x3live.GBILIVE.SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

Hope this helps you.

REgards,

Jagan.