Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI Experts
The Category and month are my dimension and Count of Category is the measure below is the table i got after calculation and Pivoting.
MonthYear | ||||||
Category | Totals | May-19 | Jun-19 | Jul-19 | Aug-19 | Jun-20 |
B1 | 4 | - | 3 | 1 | - | - |
B2 | 4 | 1 | - | 3 | - | - |
B3 | 1 | - | - | - | - | 1 |
C1 | 1 | - | - | - | - | - |
D1 | 2 | - | - | - | - | - |
I need the below calculation as in the table
Category | Totals | May-19 | Jun-19 | Jul-19 | Aug-19 | Jun-20 |
B1 | 4 | (Show the Pevious column if Null in this case 4) | 3 | 1 | - | - |
B2 | 4 | 1 (Subtract with previous column if there is a value in this case 4-1=3) | - | 3 | - | - |
B3 | 1 | - | - | - | - | 1 |
C1 | 1 | - | - | - | - | - |
D1 | 2 | - | - | - | - | - |
and the result table i intend to get should be like below.
MonthYear | ||||||
Category | Totals | May-19 | Jun-19 | Jul-19 | Aug-19 | Jun-20 |
B1 | 4 | 4 | 1 | 0 | 0 | 0 |
B2 | 4 | 3 | 3 | 0 | 0 | 0 |
B3 | 1 | 1 | 1 | 1 | 1 | 0 |
C1 | 1 | 1 | 1 | 1 | 1 | 1 |
D1 | 2 | 2 | 2 | 2 | 2 | 2 |
Can someone help me, please.
Hi
Not sure but try this using before() and if () statement as mesure
May be somethink like this:
if(isnull(sum(sales),before(sum(sales)),before(sum(sales))-sum(sales))
bruno
if(isnull(sum(sales)),before(sum(sales)),before(sum(sales))-sum(sales))
Thanks Bruno
All the values shows blank ,NOt working
Hi
Sorry to read that , can you provide a sample of data please
HI Bruno
The table is the same as the 1st table in my query, i have just changed the nomenclature for confidentiality.
Regards
Vinayak
Hi
Sorry after trying somes test and mesures I can't rich your desired output
Bruno