Hello,

I am trying to do a KPI chart that compares YTD sales v PYTD sales but I am getting nil for PYTD no matter what I try. What I have at the moment is:

1) A variable - vTodayPriorYear = date(addyears(max(Today()),-1),'DD/MM/YYYY')

2) Expression - SUM({$< Year={'$(vPriorYear)'}, CalDate={'<=$(vTodayPriorYear)'} >} [Gross sales value])

If I remove the bold section of the expression above, I get all the sales for the prior year but once added, I get zero. In the edit expression window I am getting OK & SUM({$< Year={'2018'}, CalDate={'<=17/11/2018'} >} [Gross sales value]) so from that I know the variable is working.

How can I determine where this is falling over?

Hello,

I would like to set the month, week and quarter fallow the images

But when I changed data load manager in Qlik Sense. I changed from month/3 into week/13. It's turn out into this image down here.

Green one show weeks in Jan as 5 weeks and red one also duplicate the week 9. How can I fix this and change the form into image up there. Thank you in advance

In my dash board i am trying to connect the retry mechanism but it is not working below is the code what i am using (using the REST connection)

Set Retrycount=4;

Set LocalscriptErrorcount=0;

Do

Set ScriptErrorCount=0;

Lib Connection..................

<   MY Script  >

Drop Table;

IF ScriptErrorCount>0 Then

Let LocalScriptErrorCount=$(LocalScriptErrorCount)+1;

Else

Set LocalScritpErrorCount=0;

End IF

Loop while Mod(LocalScriptErrorCount,Retrycount)<>0; 

can you please help the code

Can you please help me the correct code.I am explain very clearly i have connection,In that it is running 100 Quries.

1,2,3.....................100.The query is running up to 70th after that connection is failed so i have to apply the Retry mechanism Here .It will be start from the 70th query not from the 1st could you please explain the code please?

Hi All

I have below script work fine.

Some time i need to only run country A. what i do is manually go to country B script comment it.
May i know how to using if then else command to handle it , so that i can just enter 1 or 2 to select which country to run.
Hope some one can advise me.

Paul Yeo

// sales COUNTRY A
sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

// sales COUNTRY B
Concatenate
LOAD
'PMC' as SOURCE,
Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [date],
[Customer Name] AS COMPANY,
[Row Total (SGD)] as sales
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\SO_PMC_2019.xlsx]
(ooxml, embedded labels, table is SO_PMC_2019);

// AR COUNTRY A
Payment:
LOAD
'TDS' as SOURCE,
@1:9T as [cust_id_ar],
left(@10:38T,25) as [company_ar],
if(right(@73:90T,1)='-', '-' & left(@73:90T, len(@73:90T)-1),@73:90T) as total_ar,
Today() - @98:110T as [total number of day due]

FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\AR_Aging.TXT (ansi, fix, no labels, header is 0, record is line);

// AR COUNTRY B
Concatenate
LOAD
'PMC' as SOURCE,
[Customer/Vendor Code] as [cust_id_ar],
[Customer/Vendor Name] as company_ar,
Today() - Date( Date#([AR Invoice Date], 'DD.MM.YY'), 'DD/YY/YYY') as [total number of day due],
[Outstanding w/o Tax (LC)] as total_ar
FROM [C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\RAW DATA SAP\AR_PMC.xlsx]
(ooxml, embedded labels, table is AR_PMC);

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:
LOAD TempDate AS date,
day([TempDate]) as [day],
num(month([TempDate])) as [month],
Y AS year
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

Hi All

My below script work fine , only issue is when i try to rename the excel TAB name from BRAND to COUNTRY_A it have issue.
The Situation :-

if i change from :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [BRAND]);
To below :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [COUNTRY_A]);

i Will keep same Erroe Massage Below :-

Field not found - <Brand_>
MapDescTDS:
Mapping LOAD
[Brand_],
[SubBrand_]
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx(ooxml,embedded labels, table is [COUNTRY_A])

When i have change excel Tab name from BRAND TO COUNTRY_A
and below :-
FROM C:\Users\Paul Yeo\OneDrive - ISDN Holdings Limited\Raw Data Input\MapBrand.xlsx (ooxml,embedded labels, table is [COUNTRY_A]);

it there any other work around ?

Hope some one can advise me.

Paul Yeo

MapDesc:
Mapping LOAD
[Brand_],
[SubBrand_]
FROM MapBrand.xlsx (ooxml,embedded labels, table is [TDS]);

// sales COUNTRY A
sales:
LOAD left(FileBaseName(), 4) AS Report_dod_1,
@50:60T as [date],
'TDS' as SOURCE,
ApplyMap('MapDesc',@282:298T,'OTHERS') as [BRAND_C],
@282:298T as [BRAND],
if(@241:248T = '2',@134:147T*-1,@134:147T) as [sales]
FROM C:\Users\Paul Yeo\Dropbox\5 QV_Final\QV_RAW\Q_DOD_2018.txt (ansi, fix, no labels, header is 0, record is line);

tmp:
LOAD
min(date) AS MinDate,
max(date) AS MaxDate
RESIDENT sales;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:
LOAD TempDate AS date,
day([TempDate]) as [day],
num(month([TempDate])) as [month],
Y AS year
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

I am developing in Qlik Sense Business, and constantly get either one of the below messages. When I say constantly, I mean about every 5 minutes. Every time you need to do a refresh or close, your last changes are gone. The machine is not left idle, I am working on a new App. 

I have a fibre line with 20g upload and down load. I also have a 4G standby router. The issue is the same no matter which service I use. I have done the rebooting of the router thing!

Has any experienced the issue and found a solution? If so, please can you share your solution. Thank you.

How do I develop a bar chart with 2 dimensions. I can easily do this in excel but couldn't do in Qlik sense. Could someone help.

uid dim1 dim2
1 yes no
2 yes yes
3 no no
4 no yes
5 yes no
9 no no

output: