Date problem in Set Analysis Expression

[manpri7078]

Hi All,

I am attaching both Sample QVW as well as sample excel file for my query which are in two parts.

1.  My sample excel file is having different sheets with sheet name as Dec 16, Jan 17, Feb 17 and so on. But all the sheets have similar format data. I need a way to dynamically load all the sheets through one load statement. I think that it is possible but I am not aware of the process. My load statement for Dec 16 is as following :

LOAD [Mon-FY], Month as Mon, LEFT(FY,4) as Period, [GL Code], Description, [Field 1], [Field 2], [Field 3], [Field 4],

     [Field 5]

FROM [Sample Excel File.xlsx]

(ooxml, embedded labels, table is [Dec 16], filters(Remove(Row, Pos(Top, 1)),Remove(Col, Pos(Top, 19)),Remove(Col, Pos(Top, 19))));

I need to load all other sheets data also through this load statement. Kindly suggest me if possible but without using ODBC.

2. I am trying to make a straight table chart but some error are there in my set analysis expression. I have made one set analysis expression for example purpose. And also I have made a text object to check whether I am doing right or wrong. There is error in my set analysis expression which I am not able to figure it out. My month selected is Jun but I want to make chart for May using variable vFirstPeriod which I have defined in an input box. Kindly Suggest me what wrong I am doing in my set analysis expression.

Regards,

Manish Prasad

[techvarun]

For Question number 1 Refer the below thread for the answer.

need to load all the sheets in excel at time ?

[arvind_patil]

HI Manish,

It's Possible Please refer below code And follow instructions which are in a comment:

Let vFileName ='C:\Users\USER-12\Desktop\Multi Sheet Excel.xls';

Temp_Sheets:

LOAD Sheet as S // Number of Sheets:like I Have 3 sheets  1 2  3 so i Give 3 Value in field Sheet

FROM

(biff, embedded labels, table is [0$]); // 0 is my defalut sheet where i mention my Total number sheet

let vMaxSheet = peek('S', -1, 'Temp_Sheets');// Count of Sheets

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD

          *

     FROM $(vFileName)

     (biff, embedded labels, table is [$(vSheetNo)$]);// Sheet Number must be in sequence like 0 1  2 

next

Thanks,

Arvind Patil

[PrashantSangle]

Hi,

modify your expression to below

1: to convert your date field into date from numeric value =Date([Mon-FY],'MMM-YY')

2: In your chart you are using sum(Kolkata)

there is no such field named Kolkata in your script. Please check.

Read below blog to learn about date

https://community.qlik.com/docs/DOC-1208

https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

[manpri7078]

Hi Prashant,

I have by mistake uploaded another qlikview document. Now I have uploaded right one.

Now please suggest what I am doing wrong in my set analysis expression.

Regards,

Manish Prasad

[PrashantSangle]

Hi,

I have checked your variable definition. In that you are inserting string so convert string to date you have to use date#()

not date()

update your expression like

SUM({<[Mon-FY]={"$(=num(Date(vFirstPeriod,'MMM-YY')))"}>}[Field 1])

and your [Mon-FY] field in num format so either floor it your script or format your set analysis as per format

like

SUM({<[Mon-FY]={"$(=num(Date(vFirstPeriod,'MMM-YY'),,'####.000000'))"}>}[Field 1])

Regards,

Prashant

[manpri7078]

Hi Prashant,

I have modified my set analysis expression as per your advice, but still no data is coming.

Please see my first chart in my modified document which is attached herewith.

Regards

Manish Prasad

[PrashantSangle]

Hi,

You are  still using date() replace it with date#().

There 2 semicolon in your expression remove it.

modify your expression as

SUM({<[Mon-FY]={"$(=num(Date#(vFirstPeriod,'MMM-YY'),'####.000000'))"}>}[Field 1])

After apply above changes still you are not getting value because there is no date available for May 17.

Regards,

Prashant

[manpri7078]

Hi Prashant,

Still no data is coming after modifying my expression as

SUM({<[Mon-FY]={"$(=num(Date#(vFirstPeriod,'MMM-YY'),'####.000000'))"}>}[Field 1]).

Although Data for May-17 is available as also from Dec-16 to Jun-17.

Please check it on my attached document and suggest me what is wrong there.

Regards,

Manish Prasad

[PrashantSangle]

Hi,

Your input value is May-17 when you convert it into date it will take it as 01-05-17 and data which you have in month may on date 05-05-17 both are not going to match.

In your script convert add below modification

monthname([Mon-FY]) as monthName

and use MonthName field in your set expression.

update your set expression like

SUM({<monthName={"$(=monthName(Date#(vFirstPeriod,'MMM-YY')))"}>}[Field 1]).

Regards,