topic Re: Date field coming as General value in qlik in Connectivity & Data Prep

Date field coming as General value in qlik

Anonymous — Sat, 13 Jan 2018 12:02:24 GMT

I have a Dates of each month in the column

When i am loading the date it is coming as a general value.

I tried changing the excel format to date. Still it is coming as a general value.

Please see the date image of the excel and QLik sense

Re: Date field coming as General value in qlik

sunny_talwar — Sat, 13 Jan 2018 21:50:27 GMT

Use The Crosstable Load to load your data and then format the date as date in a resident load

Re: Date field coming as General value in qlik

Thiago_Justen_ — Sun, 14 Jan 2018 04:01:53 GMT

As stalwar1‌ said, you need to do something like this:

TEMP:

crosstable (Date, Value)

LOAD * from YOURDB;

FINAL:

LOAD

DATE(FLOOR(Date)) As Date

Resident TEMP;

Drop Table TEMP;

Re: Date field coming as General value in qlik

Anonymous — Sun, 14 Jan 2018 05:26:42 GMT

stalwar1‌

I am already using cross table.

Kindly have a look at my script

jj:

CrossTable(Date1,Budget,6)

LOAD

"Employee Code",

Particulars,

Speciality,

"Distribution type",

Distribution_Channel,

"Year",

"42736",

"42767",

"42795",

"42826",

"42856",

"42887",

"42917",

"42948",

"42979",

"43009",

"43040",

"43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);

Load

Date(Floor(Date1)) as Date2

Resident jj;

Drop table jj;

Re: Date field coming as General value in qlik

Thiago_Justen_ — Sun, 14 Jan 2018 05:34:27 GMT

Try converting date field like this

Date (Num# (Date1))

Re: Date field coming as General value in qlik

sunny_talwar — Sun, 14 Jan 2018 05:40:29 GMT

Try with Num#() instead of Floor()

jj:

CrossTable(Date1,Budget,6)

LOAD

"Employee Code",

Particulars,

Speciality,

"Distribution type",

Distribution_Channel,

"Year",

"42736",

"42767",

"42795",

"42826",

"42856",

"42887",

"42917",

"42948",

"42979",

"43009",

"43040",

"43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);

Load

Date(Num#(Date1)) as Date2

Resident jj;

Drop table jj;

Re: Date field coming as General value in qlik

Anonymous — Mon, 15 Jan 2018 05:57:46 GMT

stalwar1‌

Thanks for the answer . It worked.

I tried using YTD script available on the community.

I am using this script for the YTD.

Sum({<Date2={'>=$(=(YearStart(AddYears(Max(Date2),0))))<=$(=addYears(Max(Date2),0)))'}>}Budget)/100000

I am not able to obtain YTD. Can you help me with this?

Re: Date field coming as General value in qlik

sunny_talwar — Mon, 15 Jan 2018 12:34:15 GMT

Try this

This Year YTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2))))<=$(=Date(Max(Date2)))"}>}Budget)/100000

LYTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2), -1)))<=$(=Date(AddYear(Max(Date2), -1)))"}>}Budget)/100000

Re: Date field coming as General value in qlik

Anonymous — Tue, 16 Jan 2018 05:53:32 GMT

stalwar1‌

YTD is not coming by using that expression.

My data consist of only 2017.

I tried both your expressions.

Re: Date field coming as General value in qlik

sunny_talwar — Tue, 16 Jan 2018 09:06:26 GMT

I see no reasons why they should not work... would you be able to share a sample where I can see it not working? or share images?

Re: Date field coming as General value in qlik

Anonymous — Tue, 16 Jan 2018 09:29:16 GMT

stalwar1‌

I have selected April

So the data should be displayed as the addition of Jan to April Budget.

But it is only showing April month's Budget

Re: Date field coming as General value in qlik

sunny_talwar — Tue, 16 Jan 2018 09:36:05 GMT

Ignore selection in Date2.Month and anyother date time related fields where you might make selections

Re: Date field coming as General value in qlik

Anonymous — Tue, 16 Jan 2018 09:50:03 GMT

stalwar1‌

Thanks

Re: Date field coming as General value in qlik

Anonymous — Wed, 24 Jan 2018 06:57:13 GMT

stalwar1‌

I am using your equation with -2 for 2016 ytd.

its not working

Re: Date field coming as General value in qlik

sunny_talwar — Wed, 24 Jan 2018 10:05:37 GMT

That's not great, but you will have to share more details than just saying that it isn't working

Re: Date field coming as General value in qlik

Anonymous — Mon, 29 Jan 2018 09:13:56 GMT

stalwar1 This is my equation.

I tried it without putting Year1={"2016"} and still its not working..

if(variab='1',sum({1<Billing_Date={">=$(=Date(YearStart(Max(Billing_Date),-2)))<=$(=Date(Max(Billing_Date)))"},Year1={"2016"}>}if((Billing_Type='L2' or Billing_Type='S2' or Billing_Type='YRF2' or Billing_Type='ZEX' or Billing_Type='ZF2' or Billing_Type='ZGST' or Billing_Type='ZL2' or Billing_Type='F2'),NetValue/100000))+sum({1<Billing_Date={">=$(=Date(YearStart(Max(Billing_Date),-2)))<=$(=Date(Max(Billing_Date)))"},Year1={"2016"}>}if((Billing_Type='ZG2' or Billing_Type='G2' or Billing_Type='RE' or Billing_Type='S1' or Billing_Type='YBRE'),(NetValue*-1)/100000))

,sum({<Year1={"2016"}>}if((Billing_Type='L2' or Billing_Type='S2' or Billing_Type='YRF2' or Billing_Type='ZEX' or Billing_Type='ZF2' or Billing_Type='ZGST' or Billing_Type='ZL2' or Billing_Type='F2'),NetValue/100000))+sum({<Year1={"2016"}>}if((Billing_Type='ZG2' or Billing_Type='G2' or Billing_Type='RE' or Billing_Type='S1' or Billing_Type='YBRE'),(NetValue*-1)/100000)))

Re: Date field coming as General value in qlik

sunny_talwar — Mon, 29 Jan 2018 12:47:13 GMT

Don't really know... would you be able to share a sample?

Re: Date field coming as General value in qlik

Anonymous — Tue, 30 Jan 2018 06:55:07 GMT

stalwar1‌

i want 2016 YTD.

Start date should be 1/1/2016 and End date will be 31/12/2016

My equation is

sum({1<Billing_Date={">=$(=Date(YearStart(Max(Billing_Date),-2)))<=$(=Date(Max(Billing_Date)))"},

Year1={"2016"}>}value)

if i select any month the value should be shown till that month

Re: Date field coming as General value in qlik

sunny_talwar — Tue, 30 Jan 2018 12:31:30 GMT

May be like this

Sum({1<Billing_Date={">=$(=Date(YearStart(Max(Billing_Date),-2)))<=$(=Date(AddYears(Max(Billing_Date), -2)))"},

Year1={"2016"}>} value)

Re: Date field coming as General value in qlik

Anonymous — Wed, 31 Jan 2018 12:19:45 GMT

stalwar1‌

This is showing the data of january 2016 till 31/01/16

I want it to show the data till 31/12/2016