Need help with the fiscal year comparison

[amol]

Hello,

I have a Fiscal Year Data available with me in millions. I want to compare my data in such a manner that the trend in line graph should only show the count from the previous fiscal year. Here my X-axis should be fiscal years and Y- axis should be the count

For example: In the below table: in the fiscal year 2020-2021, 2 orders got repeated from 2019-2020. 

In the fiscal year 2019-2020, 2 orders got repeated from 2018-2019 and so on...

My Dimension is Fiscal Year and I am stucked  with my measure where my line graph should have count of previous fiscal year in this. 

2015-2016	2016-2017	2017-2018	2018-2019	2019-2020	2020-2021
ABCDE	MNOPD	TRAYL	LMNOT	TOLON	SOLOM
GHIJKL	GHIJKL	ABCDE	ABCDE	ABCDE	ABCDE
SNOUT	LMNOP	NOPKE	FGHIJ	MNOPD	LOPQKK
TNOVE	TNOVE	LMNOP	LMNOP	LOPQKK	LMNOP
SLOTE	SUVTW	STONE	SUVTW	SUVTW
SMNOPE		VTONE	KLNOZ

Any help will be really appreciated.

[Ksrinivasan]

hi,

i have taken two field data table, Fiscalyear and Countt,

No need to touch anything, just update your table and reload,

except this script line

If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;

here you should replace '2015-2016', with very first fiscalyear in your real data and count '6'.

for example: in your real table the first Fiscal year is 2010-2011 and no.of count is 10

then change the script as below:

If(Fiscalyear='2010-2011','10',AutoNumber(RowNo(),COM_01)) as FINAL;

=================================================

// Script

kkk:
LOAD
Fiscalyear,
countt,
AutoNumber(Fiscalyear) as ttt
FROM [lib:/TEST/SSSS1.xlsx]
(ooxml, embedded labels, table is Sheet13);
Store kkk into [lib://TEST/Saaaa.qvd](qvd);
Drop table kkk;
DDD:
LOAD
Fiscalyear,
countt,
(ttt)-1 as ttt
FROM [lib:///TEST/Saaaa.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
Store DDD into [lib://TEST/Saaaa1.qvd](qvd);
drop table DDD;
MP:
Mapping LOAD
//Fiscalyear,
// countt,
ttt,
Fiscalyear
FROM [lib://TEST/Saaaa1.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
KKKKA:
LOAD
Fiscalyear,
countt,
ttt,
ApplyMap('MP',ttt,'ttt') as TTTT,
countt as CT1
FROM TEST/Saaaa.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
Store KKKKA into [lib://TEST/KKKKA22.qvd](qvd);
Drop Table KKKKA;
Tab1:
Load*,
If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;
LOAD
TTTT as Fiscalyear,
CT1 as countt,
TTTT&'-'&CT1 as COM_01
FROM [lib://TEST/KKKKA22.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);
Concatenate
Load*,
If(Fiscalyear='2015-2016','6',AutoNumber(RowNo(),COM_01)) as FINAL;
LOAD
Fiscalyear,
countt,
Fiscalyear&'-'&countt as COM_01
FROM [lib://TEST/Saaaa.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq)
where not Match(Fiscalyear,'ttt');
Store Tab1 into [lib://TEST/Tab1.qvd](qvd);
Drop Table Tab1;
Tab2:
LOAD
Fiscalyear,
countt,
COM_01,
FINAL
FROM [lib://EST/tab1.csv]
(txt, utf8, embedded labels, delimiter is ',', msq)
where (FINAL>'1');

Result will be:

most of the Qlik community wiseman's requirements always like iceberg.

ksrinivasan.

[joseph_morales]

Hi @amol 

You could also try this way, I'm loading the data inline, you just have to replace it with your data.

//load the data, and create an id for the Fiscal Year field.
TEMP_DATA:
LOAD *,
AutoNumber([Fiscal Year])		as [ID_Fiscal Year];
LOAD * INLINE [
Fiscal Year, Orders
2015-2016,ABCDE
2015-2016,GHIJKL
2015-2016,SNOUT
2015-2016,TNOVE
2015-2016,SLOTE
2015-2016,SMNOPE
2016-2017,MNOPD
2016-2017,GHIJKL
2016-2017,LMNOP
2016-2017,TNOVE
2016-2017,SUVTW
2017-2018,TRAYL
2017-2018,ABCDE
2017-2018,NOPKE
2017-2018,LMNOP
2017-2018,STONE
2017-2018,VTONE
2018-2019,LMNOT
2018-2019,ABCDE
2018-2019,FGHIJ
2018-2019,LMNOP
2018-2019,SUVTW
2018-2019,KLNOZ
2019-2020,TOLON
2019-2020,ABCDE
2019-2020,MNOPD
2019-2020,LOPQKK
2019-2020,SUVTW
2020-2021,SOLOM
2020-2021,ABCDE
2020-2021,LOPQKK
2020-2021,LMNOP
];

//concatenate all orders with the separator "|" and add +1 to the field ID_Fiscal Year, 
//to assign the contamination of the orders to the next period.
left join (TEMP_DATA)
Load
[ID_Fiscal Year]+1 		as [ID_Fiscal Year],
Concat(Orders,'|')		as Concat_Orders
resident TEMP_DATA
group by [ID_Fiscal Year]
order by [Fiscal Year] asc
;


//With substringcount I will be able to determine which of the orders is 
//being repeated with the previous fiscal year.
DATA:
Load *,
SubStringCount(Concat_Orders,Orders)   as [Exists Repeated]
Resident TEMP_DATA;

Drop table TEMP_DATA;
Drop field Concat_Orders,[ID_Fiscal Year] from DATA;

[amol]

Hi @joseph_morales ,

The data is not limited. It is in Millions. The above code won't work.

Amol

[Ksrinivasan]

hi,

find the script

Tab1:
LOAD
Fiscalyear,
(Left(Fiscalyear,4)+1) &'-'& (Left(Fiscalyear,4)+2) as Fiscalyear_Du,
countt
FROM [lib://TEST/SSSS1.qvd]
(ooxml, embedded labels, table is Sheet13);
Concatenate
Load
Fiscalyear_Du as Fiscalyear,
countt
Resident Tab1;
Store Tab1 into [lib://TEST/Tab2.qvd](qvd);
Drop Tables Tab1;
Tab2:
LOAD
Fiscalyear,
// Fiscalyear_Du,
countt,
if(Fiscalyear='2015-2016','2',AutoNumber(RowNo(),Fiscalyear&'-'&countt)) as CC_Nt
FROM [lib:///TEST/tab2.qvd]
(txt, utf8, embedded labels, delimiter is ',', msq);

chart:

Dim: =Fiscalyear

Measure: =count( {$<CC_Nt={"=(CC_Nt)>1"}>} CC_Nt )

result:

ksrinivasan

[amol]

Thank you so much @Ksrinivasan . I will try to implement the above logics and get back to you.

Amol