Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Month and Year from Date Field

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

MonthYearSQL.jpg

MonthYear.jpg

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Re: Month and Year from Date Field

Resolved:

InvoiceLines:

LOAD

    "CPY_0" AS [Company],

    "SALFCY_0" as [Site],

    "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,

    date("ACCDAT_0", 'DD/MM/YYYY') as AccDate,

    "Month" as InvMonth,

    "Year" as InvYear,

    "Period",

    "FIY" as Year,

    "Period"&"FIY" as PerYr

    ;

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

;

View solution in original post

15 Replies
Highlighted
Creator III
Creator III

Re: Month and Year from Date Field

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.

Highlighted

Re: Month and Year from Date Field

Hi,

try this

MonthName(Yourdatefield) as MonthYear.

let me know  if that worked.

Regards

ASHFAQ

Highlighted

Re: Month and Year from Date Field

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

Highlighted
Master III
Master III

Re: Month and Year from Date Field

Can you post QV script you tried

Highlighted
Creator II
Creator II

Re: Month and Year from Date Field

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.

Highlighted
Creator II
Creator II

Re: Month and Year from Date Field

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

Highlighted
Master III
Master III

Re: Month and Year from Date Field

"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

Highlighted
Creator II
Creator II

Re: Month and Year from Date Field

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."

Highlighted
MVP & Luminary
MVP & Luminary

Re: Month and Year from Date Field

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.