topic Re: Difference Querry in QlikView

Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:04:33 GMT

Hi,

I've attached the qvw and data file.

I want to differentiate between months and want to get Rupees in Debit/Credit amount, but I'm getting wrong data.

Can anyone help me on this?

Re: Difference Querry

Kushal_Chawda — Fri, 06 May 2016 11:12:00 GMT

What is the output needed?

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:15:53 GMT

Hi Murali, I didn't see any error, can you detail where is the wrong data?

Re: Difference Querry

Fri, 06 May 2016 11:18:02 GMT

Your alternate states expressions are good. May be with issues with you have negative rupees values in your data.

You may try : fabs(fabs(column(1))-fabs(Column(2))) for your difference column.

Whats was desired out put you are expecting ?

Re: Difference Querry

Gysbert_Wassenaar — Fri, 06 May 2016 11:18:02 GMT

Looks perfectly fine to me. What's wrong about the data?

Re: Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:19:10 GMT

Example:

Feb we don't have Cloths 0

Jan we have Cloths - 25 this is Credit

so Total Difference = -25

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:24:57 GMT

OK, I think the problem is when you want to do:

Column(2)-column(1)

Column(1)-column(2)

I think that you need to load months also in number so you can make a comparison before decide the formula, something like:

If(SelectMonth<CompareMonth, Column(2)-column(1), Column(1)-column(2))

Re: Difference Querry

Gysbert_Wassenaar — Fri, 06 May 2016 11:25:26 GMT

No, it's not. The difference between 0 and -25 is 25, not -25.

Re: Difference Querry

trdandamudi — Fri, 06 May 2016 11:28:39 GMT

I don't see any issue... Everything looks perfect..

Re: Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:30:00 GMT

In Feb No money Rupees = 0

In Jan we had Credit amount = -25

if we do difference still we get -25 only right?

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:31:08 GMT

In Feb you have 25 rupees more than you had in Jan.

Logic maybe can vary depending in wich selector has Feb and wich one has Jan

Re: Difference Querry

Gysbert_Wassenaar — Fri, 06 May 2016 11:34:16 GMT

That depends on how math is defined in your universe. In mine it's 0 minus -25 = 0 + 25 = 25. That's also how math is implemented in Qlikview.

Re: Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:41:20 GMT

In Jan we earned 17 Rupees

In Feb we spent -36 Rupees

so they left out credit balance is -19 right?

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:44:30 GMT

No Murali, in Jan you have a positive 17, in Feb you have a negative 36, that's a difference of 53, the symbol (positive or negative) will depend on the order of the operators, but the '53' will be kept.

Re: Difference Querry

Gysbert_Wassenaar — Fri, 06 May 2016 11:44:48 GMT

Yes, the difference between -36 and 17 is -19: -36 + 17 = -19. If both numbers are credit amounts then the credit balance between the two is -19.

Perhaps this helps: Adding & subtracting negative numbers | Negative numbers |Khan Academy

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:46:15 GMT

Or maube you're asking about cummulative sums, not differences, in that case use:

Column(2)+column(1)

Re: Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:50:01 GMT

How to use cumulative here?

just Column(2)+column(1) ???

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 11:53:36 GMT

That will sum the value from the first column with the value in the second column.

Note that's not a difference, is an accumulation, ie:

Jan Feb TOTAL

10 15 25

-10 5 -5

Re: Difference Querry

MuraliPrasath — Fri, 06 May 2016 11:58:29 GMT

thank you, I was so stupid today. lol

Re: Difference Querry

rubenmarin — Fri, 06 May 2016 12:07:46 GMT

Glad to help.

Think twice about your final expression, if you're using alternate states and one is called 'comparemonth' maybe the intention should be to check the differences, not an accumulation (this can be done with only one state, no need of altern states).

Just quietly review your requirements and the answers in this thread to find the optimal solution.