Thank you all for the great community support.  I have learned a lot in a short time through searching for answers.  But I am stuck.

I am Trying to Create a Graph that shows Q1, Q2, Q3, and Q4 for 2018, 2019 which then shows the total number of Member Months for those Quarters by Year

I have a Field called Period which includes the Values 2018Q1, 2018Q2,...2019Q3.  When I make a table and put Period and Member Months I get the appropriate values by Period. But when I edit the Period I get total and they repeat for every Period.

Period           Member Months

2018Q1                 5

2018Q2                 7

2018Q3                 6

2018Q4                 5

2019Q1                  9

2019Q2                 8

When I change Period to be =right(Period, 2) and also create =left(Period, 4) I get:

Year    Quarter     Member Months

2018      Q1              40

2018       Q2             40

2018       Q3              40......

How do I get 2018 Q1 to show 5?

If I add Period back into the table everything works as expected (except I can't graph that way).

Hello,

I Need help into calculate count of at the start of month and at the end of month,

am having HR data with me like

Empcode,

DOJ, Emp Left date,
Active/ Inactive Flag (if emp is working then it will be active else it will be Inactive)

Output should be like -
for april month opening count will be last year's March Month's closing count.
for may Month opening stock will be April Month's Closing stock ect..

Thanks in advance

I have a Date field in the script  and a Month field. Month field values are "M000", "M001", "M002" and up to "M060"

I would like to name those fields as Date + 30 day incremental.

For example "M000" should be Monthname(Date +0 )

"M001" should be Monthname(Date +30 )

"M002" should be Monthname(Date +60 ) and so o so forth

I can do that with a If statement like 

If(Month='M000',Monthname(Date+0),
If(Month='M001',Monthname(Date+30),
 If(Month='M002',Monthname(Date+60)))) etc .

But I am looking for a more robust solution using a For loop or something so I don't have to do 60 if statements.

Can anyone help. Please see the attached sample file

Hello togehter,

in Excel it is easy to see which tab the page feature uses because of the changed tab name.

In PowerPoint I can't find any information about which page the feature uses.

Is there a way to find out, or is there a hint somewhere?

Thanks in advance.

Greetings

Hi there. I am going to try to be as descriptive as possible with this issue. I have noticed there are a number of threads on similar / tangential matters, but none really appeared to resolve the issue I am facing.

I am relatively new to Qlik Sense (or View for that matter).

As the title suggests, the scenario is the following:

I am developing an app that relies on data :

found in 'n' number of .xslx workbooks. These workbooks are structurally identical and from there the app loads 4 sheets, that, taken together form the bulk of the data set.

these sheets reside on an internal SharePoint location.

What I seek to achieve is be able to have a connection to the SharePoint folder and load the 4 sheets from the 'n' number of workbooks present there.

I have established a REST connection to the SharePoint in question as such:

URL: https://domain/subsite1/subsite2/_api/web/GetFolderByServerRelativeUrl(' folder1/folder2/3.0 folder3/1.0 folder4/test_data’)/Files 

Method: GET

Authentication: Windows NTLM

Connects fine to above. No pagination settings yet. What I can retrieve from the above is in effect a list of URLs for the files present there, as such:

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile1.xlsx')

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile2.xlsx')

etc.

I understand that one can't use a wildcard load, nor does a construct like the below work:

 Let vExcelFilePath = 'lib://[SharePoint Path]/test_data/*.xlsx';

For Each vFile In FileList('$(vExcelFilePath)')

Trace $(vFile);

exampleTable:

LOAD
    "Project ID" as d_ProjectID,
    "Project Name" as d_ProjectName

   //whatever other fields etc.

      From [$(vFile)](ooxml, embedded labels, table is ProjectData);
 
Next vFile

My logic was to use the list pulled through the rest connector, loop through the ‘n’ number of excel URLs and load them via a looped Web File connection.

But as I try to connect to, say,

https://domain/subsite1/subsite2/_api/Web/GetFileByServerRelativeUrl('/subsite1/subsite2/ folder1/folder2/3.0 folder3/1.0 folder4/test_data /exampleExcelFile1.xlsx')

So the connection works but for some reason it won’t open the contents of the xlsx file.

What am I missing here? And further what should I be doing to achieve this connection / load of data? I have been scouring resources up and down and have come up empty handed, so any help will be appreciated.

Thanks

Is there no simple option to select the color of a measure's bars ? I'm using stacked bars in a combo graph.

I have been snooping around here and all I see are circumvoluted ways of achieving what should be a simple option. 

Am I missing an easy way to script "this measure is this colour and that one is that color" ?

Hi there,

I'm searching a solution to the problem below. I built a database, and now I need to link a plan table which contains plan values. How can I do this without "loop"? I would like to filter dates, tags etc. in Analysis after.

Important: one EXP ACC LINES ID can have multiple TAG ID.

Thanks in advance!

Hello, guys,

I have a problem with Left join.

Table1:

LOAD
display_name,
date(floor(calcdate)) as Date,
Year(calcdate) & ceil(month(calcdate)/3) as Y/Quarter,
ceil(month(calcdate)/3) as Quarter,
num(month(floor([calcdate]))) as "Month",
year(floor(calcdate)) as "Year",
year(floor(date(calcdate)))&num(month(floor(date(calcdate))))&plate_nr as Key,
plate_nr,
FROM [lib://KKK/Detali*.xlsx]
(ooxml, embedded labels, table is Sheet)
where year(calcdate)>=2018;

Table2:

Left join (Table1)
LOAD
year(date("accident_date"))&num(month(date("accident_date")))&"reg_plate" as Key,
"Rental_car"

FROM [lib://KKK/Trail*.xlsx]
(ooxml, embedded labels, table is Sheet2)
where "Rental_car"<>0;

I've created a Key based on Year, month and plate number. Plate numbers are Unique, but they can appear in, for example, 2018 08 and later on 2019 01, so I need to join on dates too. So the problem here is, that it only joins on 2018 year. After the join i can only find "Rental_car" values for 2018 year, but not for the 2019. I know for sure, that it should be values for the 2019 values, too. Next, if for the Table2 I only take from the 2019 excel file (not from the 2018-2019), then I can see those values for "Rental_car", so what the hell is this? Why it's only joining on the lowest year of the data, but not for all..?

Sorry for my english, if i wasn't clear enough I can try to explain more.

Thank you.

Hi All,

I am new to QlikSense.

While uploading data (Excel Sheet), I am getting error regarding File Size.

I am trying to upload file of size 278MB and it's throwing an error saying 'Maximum File Size to upload is 50 MB'.

Could you please help/suggest to resolve this issue?

Thanks,

Dhanashree

Hi all,

I have following data in my app, with both fields not related to each other:

In the app, we have a filter on 'Customer status', which is always on value ACTV.

I have a bar chart which shows data on the 'Activity Type' (stacked bar chart with color coding in function of the 'Activity Type').

- When Customer status = ACTV is not selected, the colors for each 'Activity Type' work correctly.
- but when Customer status = ACTV is selected (default), the colors are not shown. So I try to find a way to make that dimension ignore the filter on Customer status.

I have tried multiple solutions to have the Color by Dimension ignore the filter on [Customer status], but they don't seem to work:

- aggr(only({<[Customer status]=>} [Activity Type]),[Activity Type])
- concat({<[Customer status]=>} distinct [Activity Type], ',')
- aggr(concat({<[Customer status]=>} distinct [Activity Type],','),[Activity Type])

Do you have any idea what other possible solutions I could try?