topic Re: varience in QlikView

varience

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

HI Experts,

I have pivot table sales ,here i want variance like below

loveisfail‌avinashelite‌jagan‌kush141087‌vikasmahajan‌

Monthname,Sales

Jan,100

Feb,500

Mar,1000

Apr,2000

May,2500

June,3500

Expected O/P:-

400

500

1000

500

1000

regards

Re: varience

its_anandrjs — Sat, 19 Aug 2017 07:26:50 GMT

Try this ways

Dim:- Monthname

Expression:- sum(Sales) - Above(Sales)

Regards

Anand

Re: varience

its_anandrjs — Sat, 19 Aug 2017 07:33:35 GMT

Check the attached file for this

1. First ways Sum(Sales) - Above(Sales)

2. Second ways Fabs(sum(Sales) - Below(Sales) )

Regards

Anand

Re: varience

qlikview979 — Sat, 19 Aug 2017 09:51:51 GMT

Hi all <

how can i do in back end

Regards

Re: varience

Anil_Babu_Samineni — Sat, 19 Aug 2017 09:55:27 GMT

Why not this?

LOAD *,Sales - Previous(Sales) as PrevSales; // you can use peek if needed.

LOAD * Inline

[

Monthname,Sales

Jan,100

Feb,500

Mar,1000

Apr,2000

May,2500

June,3500

];

Re: varience

effinty2112 — Sat, 19 Aug 2017 09:59:43 GMT

Hi Mahesh,

Data:

LOAD

Monthname,

Sales,

Sales - Previous(Sales) as ChangeInSales;

LOAD * Inline [

Monthname,Sales

Jan,100

Feb,500

Mar,1000

Apr,2000

May,2500

June,3500

];

Gives

Monthname	Sales	ChangeInSales
Jan	100
Feb	500	400
Mar	1000	500
Apr	2000	1000
May	2500	500
June	3500	1000

cheers

Andrew

Re: varience

antoniotiman — Sat, 19 Aug 2017 10:12:33 GMT

Hi Mahesh,

You need Order By if Monthname isn't sorted, like this

Table:
LOAD Month(Date#(Monthname,'MMM')) as Monthname,Sales Inline [
Monthname,Sales
Apr,2000
May,2500
June,3500
Feb,500
Mar,1000
Jan,100];
Join LOAD *,RangeSum(Sales-Peek(Sales)) as Variance
Resident Table Order By Monthname;

If You want 100 as Jan Variance then RangeSum(Sales,-Peek(Sales)) // , added

Regards,

Antonio

Re: varience

its_anandrjs — Sat, 19 Aug 2017 14:00:10 GMT

As you ask this in the Backend part then go this way

LOAD *,
RowNo() as RID,
Sales - Previous(Sales) as [Expected O/P];

LOAD * Inline
[
Monthname,Sales
Jan,100
Feb,500
Mar,1000
Apr,2000
May,2500
June,3500
];

See attached

Re: varience

qlikview979 — Mon, 21 Aug 2017 07:35:11 GMT

Hi All,

Sorry for delay

i am getting correct output in front end ,i need same thing i back end

I have Date column instead of Monthname ,but i want here monthname wise in back end

Regards

Re: varience

Anil_Babu_Samineni — Mon, 21 Aug 2017 07:38:35 GMT

I would ask you to buy qlik cook book or read some fundamentals before using qlikview.

And for your question?

MonthName(DateField) as MonthName //Month(DateField) as MonthName (Just use this)