Calculating the difference in Value when we select two weeks dynamically

[#1]

Hi All,

I need to calculate difference of two weeks for all expressions in a pivot table. There are about 10 expressions. always there are two weeks selected. The selection of week should be dynamic. I created two variables for min and max week but unable to add difference.

Thanks in advance for your help.

Please see the data and output example in attached file.

Regards,

Veman Reddy

[#2]

Any help

[johnw]

Perhaps this? For this approach, don't include Week as a dimension. You're simulating it with additional expressions.

sum({<Week={'$(=min(Week))'}>} Amount)
sum({<Week={'$(=max(Week))'}>} Amount)
column(2)-column(1)
sum({<Week={'$(=min(Week))'}>} Exp)
sum({<Week={'$(=max(Week))'}>} Exp)
column(5)-column(4)

[#4]

Hi John,

Thanks for your reply.

Here are two cases where I am facing problem.

1. When there is NULL value for amount for a loc for week, the record is not displaying,

2. There may be chance of Alphanumeric value for amount.

3. The week should be dynamicallky selected.

Your help is much appreciated.

I attached excel spread sheet for your reference.

Regards,

Veman

[johnw]

Mmmm, right? Unless I'm misunderstanding, what I gave you does that. See attached using the expressions I gave you with the data you gave me, with a minor fix because I forgot that the min() and max() functions when used on dates lose the date formatting, so I had to wrap them in the date() function to get it back.

1. Nulls - Uncheck "suppress nulls" and "suppress 0 values". Then locs C and D will show up despite the nulls. But I'm unclear if you really wanted that, since that's not what your Excel file shows. In any case, if you want the rows, don't suppress them. If you don't want the rows, suppress them.
2. Alphanumeric values - What exactly do you want to happen? If you give it "ABC", it treats it as 0 for numeric sums and comparisons. I assume that's what you want.
3. Dynamic selection of weeks - Yes. Just select two weeks, and they'll be compared. That's what the min() and max() are doing.

[#6]

Hi John,

Thanks for your support

[#7]

Hi John,

I have some columns where data type is text and for some it is alpha numeric.

I attached the exact requiremnt in the excel spread sheet.

When there is change w.r.t. min week there should be change in the colour of background.

Thanks on advance.

Regards,

Veman Reddy

[johnw]

OK. Same basic idea. Just use only() instead of sum() and compare the two values in a color expression instead of making a separate column for the comparison. See attached.

[#9]

Hi John,

Thanks for your reply.

I have a problem in sorting the values. I am not getting the idea to sort as per the requirement.

The requirement is , Let imagine comparing values for 3 months.

The order od value should be, Only the value of min month, then only the values of second month and so on. Then, Two months, then 3 months etc.

The user may select the month dynamically. It may be 6 month or 10 etc.

I have attached excel spread sheet with data and sorting pictorial diagram for your reference.

Thanks in advance.

Regards,

Veman Reddy

[johnw]

Sorry, I can't tell what your chart is doing. As a quick for instance, if I select Loc = T, the first tab tells me 783 revenue in August and 5436 revenue in December. In your chart, you show 17 revenue in July (which appears to be the revenue for AP in July), the correct 783 in August, and then December is missing. Well, it isn't missing; it's being given to AP, so it's like AP and T switched revenue across a couple months. Anyway, no clue what you're doing.